Difference between different different values depending on filter criteria

Hi Anonymous

In query editor, please pay attention to the bold characters which you can use for your scenario.

Code in advanced editor,

let

    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.20\Difference between.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fare Type", type text}, {"Route", type text}, {"Month", type date}, {"Company", type text}, {"Lead in fare", Int64.Type}}),

    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CompanyA", each if Text.Contains([Company], "A") then [Lead in fare] else null),

    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"CompanyA"})

in

#"Filled Down"

Then in Data Model View, create a calculated column

differentialA-B = [CompanyA]-[Lead in fare]

Best Regards

Maggie