Power-Query and Power BI How to create a measure and union it back to the original data-set

Question

Wondering how we create a measure based on a data-set, then union it back to the original data-set ?

May be it make it easier to view the problem visually, so i have created the picture below.

P.S. Somehow i have to do it in this way in regardless of previousday OR related function

thanks

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-26T14:45:22

You can add a custom column to calculate the comparison volume by looking up the volume on the last date before the current row's date for that category value. Remove the Volume column, rename the CalcType from "Individual" to "Comparison" and then append that table to your original table.

Here's what the M code would look like.

let
    Source = <Insert Source Here>,
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Date", type date}, {"Category", type text},
         {"CalcType", type text}, {"Volume", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Comparison", each
        try Table.Max(Table.SelectRows(#"Changed Type",
            (C) => C[Date] < [Date] and C[Category] = [Category]), "Date")[Volume]
        otherwise null, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Volume"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Comparison","Volume"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",
        "Individual","Comparison",Replacer.ReplaceText,{"CalcType"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Volume] <> null)),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Filtered Rows"})
in
    #"Appended Query"

Power-Query and Power BI How to create a measure and union it back to the original data-set

1 Answers