Forum Discussion

Regular Visitor

6 years ago

Solved

Power query : Column header with Volume & Year

Hello Power BI community, Beginner in Power BI and former user of Pivot table in excel, I am having a file with component yearly volume and vehicle yearly volume column (please see picture)...

Need Help

Tips and Tricks

dax

6 years ago

Hi ClementA ,

You could refer to my sample for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYjMgNoJicygNkktUitWJhsuYArEJFJtCdYFUJYFVwURh5plBVVpCVSUrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"C Volumn 2012" = _t, #"C Volumn 2013" = _t, #"C Volumn 2014" = _t, #"C Volumn 2015" = _t, #"R Volumn 2012" = _t, #"R Volumn 2013" = _t, #"R Volumn 2014" = _t, #"R Volumn 2015" = _t, name = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "type"}, {"Attribute.3", "date"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

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

0813.pbix71 KB

dax

Community Support

6 years ago

Hi ClementA ,

You could refer to my sample for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYjMgNoJicygNkktUitWJhsuYArEJFJtCdYFUJYFVwURh5plBVVpCVSUrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"C Volumn 2012" = _t, #"C Volumn 2013" = _t, #"C Volumn 2014" = _t, #"C Volumn 2015" = _t, #"R Volumn 2012" = _t, #"R Volumn 2013" = _t, #"R Volumn 2014" = _t, #"R Volumn 2015" = _t, name = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "type"}, {"Attribute.3", "date"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

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

0813.pbix71 KB