Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
Thank you!
That's great help!
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.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |