Joining Headers in one table with a Column in another

[Anonymous]

Hi All

So, I have a list of countries as a table with each country being a header of a column and having some values within said column

I am trying to bring it into the model and link it with a fact table I have, which also contains a list of countries but as a column

So, essentially a table like this:

To be joined to they key fact table in the model that has list of countries as a row

Any advise will be greatly appriciated! 

[Anonymous]

Thank you!

That's great help!

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

These countries cannot be associated with another table when they are columns, you can convert them into rows in Power Query and then establish relationships, and then enter the following code in Advanced Editor

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUTICYhMgNlWK1YGImAKxMVgUJGICFQHR5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Argentina = _t, Armenia = _t, Brazil = _t, Ghana = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Argentina", Int64.Type}, {"Armenia", Int64.Type}, {"Brazil", Int64.Type}, {"Ghana", Int64.Type}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),

    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Country"}})

in

    #"Renamed Columns"

The report is then applied to the report, and then a relationship is established with another table

Best Regards,

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}