Forum Discussion

Lara137's avatar
Lara137
New Member
2 years ago
Solved

Power BI : data transformation with Power query

Hello everyone!   I'm new in Power BI and should to transform data in Power query for convinient analizing. I need to have one column for date (years), one column for Units and one column for sale...
Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY4xDoAgDEXv0pnBYgv0DE7GOBHi/W9hWwMxQQfeQF9/f63A17GdEGBHReRVWSIrF38tvJSoQB9ykm/F921GfyGkkKJInL8NHN927vF5btIVD8uEc5FuZMtIwnORkWFVhAlauwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sku name" = _t, Quarter = _t, #"2019, Units" = _t, #"2019, EUR" = _t, #"2020, Units" = _t, #"2020, EUR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sku name", type text}, {"Quarter", type text}, {"2019, Units", Int64.Type}, {"2019, EUR", Int64.Type}, {"2020, Units", Int64.Type}, {"2020, EUR", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sku name", "Quarter"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

     

    Copy and paste the full example into your advanced editor.

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY4xDoAgDEXv0pnBYgv0DE7GOBHi/W9hWwMxQQfeQF9/f63A17GdEGBHReRVWSIrF38tvJSoQB9ykm/F921GfyGkkKJInL8NHN927vF5btIVD8uEc5FuZMtIwnORkWFVhAlauwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sku name" = _t, Quarter = _t, #"2019, Units" = _t, #"2019, EUR" = _t, #"2020, Units" = _t, #"2020, EUR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sku name", type text}, {"Quarter", type text}, {"2019, Units", Int64.Type}, {"2019, EUR", Int64.Type}, {"2020, Units", Int64.Type}, {"2020, EUR", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sku name", "Quarter"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

 

Copy and paste the full example into your advanced editor.