Forum Discussion
Anonymous
4 years agoNot applicable
Can PowerBI do this?
I have a sample dataset like this month salesperson Iphone this year Iphone last year Ipad this year Ipad last year Aug bob 20 15 30 25 Aug evan 40 35 50 45 I need to...
- 4 years ago
First step is to bring the data into a usable format.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNV9JRSspPApJGBkDC0BRIGINYRqZKsTowFalliXlAygQkYQxSYgpimQCVxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, salesperson = _t, #"Iphone this year" = _t, #"Iphone last year" = _t, #"Ipad this year" = _t, #"Ipad last year" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"month", "salesperson"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Product"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","this year","2021",Replacer.ReplaceText,{"Attribute.2"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","last year","2020",Replacer.ReplaceText,{"Attribute.2"}), #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.FromText([month] & " 1 " & [Attribute.2])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"salesperson", "Product", "Value", "Date"}) in #"Removed Other Columns"yields
That's something you can load into Power BI.
If you want to show the change you usually subtract the previous value from the current value. Your example seems to indicate that you want to calculate it the other way round?
lbendlin
4 years agoSuper User
First step is to bring the data into a usable format.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNV9JRSspPApJGBkDC0BRIGINYRqZKsTowFalliXlAygQkYQxSYgpimQCVxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, salesperson = _t, #"Iphone this year" = _t, #"Iphone last year" = _t, #"Ipad this year" = _t, #"Ipad last year" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"month", "salesperson"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Product"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","this year","2021",Replacer.ReplaceText,{"Attribute.2"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","last year","2020",Replacer.ReplaceText,{"Attribute.2"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.FromText([month] & " 1 " & [Attribute.2])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"salesperson", "Product", "Value", "Date"})
in
#"Removed Other Columns"
yields
That's something you can load into Power BI.
If you want to show the change you usually subtract the previous value from the current value. Your example seems to indicate that you want to calculate it the other way round?
Anonymous
4 years agoNot applicable
Thank you very much!