Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to transform my original table shown below:
into something like shown below:
Is it possible to do in Power Bi? If so, please guide me how to do it.
Thank you
Solved! Go to Solution.
Hi @enoch99 ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUXIEYkNTEGEAIoxAhDGMZWyqFKuDrNoJiI1ACo3AWsxBhAlMi5ExmmpnmLEgdRYgjhmQMAURJhCTfROLkjOgrjCHqQartIAYCFSOohDkAJB+S5AaQxgD5FYDFHVgq8G6od4zQJgeGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Location = _t, #"Boy(1-17)" = _t, #"Girl(1-17)" = _t, #"Male Adult(18-59)" = _t, #"Female Adult(18-59)" = _t, #"Male Elderly(60+)" = _t, #"Female Elderly(60+)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Location", type text}, {"Boy(1-17)", Int64.Type}, {"Girl(1-17)", Int64.Type}, {"Male Adult(18-59)", Int64.Type}, {"Female Adult(18-59)", Int64.Type}, {"Male Elderly(60+)", Int64.Type}, {"Female Elderly(60+)", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Location"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",")","",Replacer.ReplaceText,{"Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute.2", "Age"}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Attribute.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.1.2"}), #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","Boy","Male",Replacer.ReplaceText,{"Attribute.1.1"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Girl","Female",Replacer.ReplaceText,{"Attribute.1.1"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute.1.1]), "Attribute.1.1", "Value", List.Sum) in #"Pivoted Column"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @enoch99 ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUXIEYkNTEGEAIoxAhDGMZWyqFKuDrNoJiI1ACo3AWsxBhAlMi5ExmmpnmLEgdRYgjhmQMAURJhCTfROLkjOgrjCHqQartIAYCFSOohDkAJB+S5AaQxgD5FYDFHVgq8G6od4zQJgeGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Location = _t, #"Boy(1-17)" = _t, #"Girl(1-17)" = _t, #"Male Adult(18-59)" = _t, #"Female Adult(18-59)" = _t, #"Male Elderly(60+)" = _t, #"Female Elderly(60+)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Location", type text}, {"Boy(1-17)", Int64.Type}, {"Girl(1-17)", Int64.Type}, {"Male Adult(18-59)", Int64.Type}, {"Female Adult(18-59)", Int64.Type}, {"Male Elderly(60+)", Int64.Type}, {"Female Elderly(60+)", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Location"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",")","",Replacer.ReplaceText,{"Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute.2", "Age"}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Attribute.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.1.2"}), #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","Boy","Male",Replacer.ReplaceText,{"Attribute.1.1"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Girl","Female",Replacer.ReplaceText,{"Attribute.1.1"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute.1.1]), "Attribute.1.1", "Value", List.Sum) in #"Pivoted Column"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |