Create rows from columns

[enoch99]

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

[tackytechtom · Accepted 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/

[enoch99 · Accepted solution]

Hi @tackytechtom,

thank you very much. Exactly what I wanted.

[tackytechtom · Accepted 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/