Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • lbendlin's avatar
    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?