PQ - Details for selected country

[sandy666]

Details for selected country

Country

Area

Building permits

Capital

Country/region

Abbreviation

Name

Population

Persons per household

Time zone(s)

Country

Attribute

Value

Alabama

135,765

Montgomery

US-AL

Alabama

4,874,747

2.55

Central Time Zone

Indiana

Building permits

18713

California

102,350

US-CA

California

39,536,653

Pacific Time Zone

Capital

Indianapolis

Florida

170,304

Tallahassee

United States

Florida

21,670,000

2.63

Country/region

United States

Hawaii

28,311

3,369

US-HI

1,427,538

Hawaii-Aleutian Time Zone

Population

6666818

Illinois

149,998

Illinois

2.63

Central Time Zone

Indiana

18,713

Indianapolis

United States

6,666,818

Nevada

286,367

17,952

Carson City

United States

US-NV

Nevada

2.72

Mountain Time Zone, Pacific Time Zone

New Jersey

22,608

26,793

United States

US-NJ

9,005,644

2.73

New York

Albany

US-NY

New York

Oregon

255,026

19,586

United States

US-OR

Oregon

4,142,776

2.51

Mountain Time Zone, Pacific Time Zone

Texas

Austin

US-TX

28,304,596

2.84

Washington

184,827

44,077

Olympia

Washington

7,405,743

2.56

Pacific Time Zone

// Country
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

// Data
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Country"}, "Attribute", "Value"),
    Filter = Table.SelectRows(UOC, each [Value] <> null and [Value] <> "")
in
    Filter

// Result
let
    Source = Table.NestedJoin(Country,{"Country"},Data,{"Country"},"Data",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Data", {"Attribute", "Value"}, {"Attribute", "Value"}),
    ROC = Table.SelectColumns(Expand,{"Attribute", "Value"})
in
    ROC