Need help for generating raw wise difference from column using DAX or power query

Hey Nicks612 ,

Glad to hear it worked! And that’s a great follow-up question regarding handling differences across many columns. If you’re dealing with 25+ columns, creating individual formulas for each can be tedious and error-prone. While DAX is powerful for dynamic measures, it isn’t ideal for row-wise comparisons across multiple columns. Instead, I’d suggest sticking with Power Query for scalability.

Power Query Pattern

You can automate the difference calculation across all relevant numeric columns using List.Transform and Record.TransformFields.

1. Sort your data by Date & Shift.

2. Add an Index Column (Index).

3. Duplicate the table and shift the index (PrevIndex = Index + 1).

4. Merge on Index and PrevIndex.

5. In the Merge step, after expanding the previous row's values:

   • Create a list of target columns (e.g., {"Machine A", "Machine B", ..., "Machine Z"})

   • Then dynamically compute differences:

let
    columnsToCompare = {"Machine A", "Machine B", "Machine C", ...},
    AddDifferences = Table.TransformColumns(
        MergedTable,
        List.Transform(columnsToCompare, each {
            "Diff " & _, 
            each Record.Field(_, _) - Record.Field(_, "Prev." & _), 
            type number
        })
    )
in
    AddDifferences

This will create Diff Machine A, Diff Machine B, etc. for all desired columns without hardcoding each formula.

When creating your line chart combine Date + Shift into a single column for a cleaner X-axis. Like:

[Date] & " - " & [Shift]

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

This modification of my algorithm shows a method of handling multiple columns without having to hard code them.

It uses your existing data sample, which shows that the columns to be processed are all of the columns except for the first two. 

It also uses a custom function to set the data types of the resultant table.

Again, paste the code into the Advanced Editor and read the comments to understand how it works

Main Code

let

//change next line to reflect actual data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdE9CsMwDAXgq5TMLViS9XeOjiFbKe3Soc39qbCSkOCABw3+ePLzOA58wzgFebgO8Jsv99f7OceMUiuwxyRuphCDFnfDYbo2JavCz2Ov1MCacmBrqqB4p+i7VwykmgqrpyIpq9LzLFZjTqWiqUytU8csAW4qLhfMDQFo29DO2xASWiIAKRVS7bI6pYsizFCotXZZx3eJGi3VcUQ0JSKdOr5LQaCkij9IZba14ecbKinLWl2uCu7RxvQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Shift = _t, #"Machine A" = _t, #"Machine B" = _t, #"Machine C" = _t]),
    
//Written so as not to require listing all the column names
//Assumes Column one = Date; Column 2=Shift, and the rest are the columns to be processed
    colNames=Table.ColumnNames(Source),
    colTypes=List.Zip({List.Skip(colNames,2), List.Repeat({Int64.Type}, Table.ColumnCount(Source)-2)}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Shift", type text}} & colTypes),

//In your sample data, the columns to process start with column three.
//this line may need to be changed if not the case in your actual data
    #"Cols to Process" = List.Skip(colNames,2),

//Create new column names by prefacing the processed columns with "Diff ".
//Other algorithms could be used
    #"New Col Names" = List.Transform(#"Cols to Process", each "Diff " & _),

//Create lists of differences for each processed column
    #"Compute Differences" = 
        List.Accumulate(
            #"Cols to Process",
            {},
            (s,cur)=> s & {[a=Table.Column(#"Changed Type",cur),
                         b=List.Skip(a),
                         c=List.Zip({a,b}),
                         d=List.Transform(c, each _{1} - _{0})
                         ][d]}),
    
//Create the "type table" line using a custom function
    #"Table Types" = [a=Table.ColumnNames(#"Changed Type"),
                      b=#"New Col Names",
                      c=a & b,
                      d={type date, type text} & 
                            List.Repeat({Int64.Type}, List.Count(#"Cols to Process")+ List.Count(#"New Col Names")),
                      e=fnRecordTypes(c,d)][e],

//Combine the existing and new columns
    #"Combine Columns" = Table.FromColumns(
                            List.FirstN(Table.ToColumns(#"Changed Type"),2+List.Count(#"Cols to Process"))
                            & #"Compute Differences", type table #"Table Types"),

//Order the columns as you show in your sample
    #"Reorder Cols" = Table.ReorderColumns(#"Combine Columns", 
        List.FirstN(Table.ColumnNames(#"Changed Type"), 2) & List.Combine(List.Zip({#"Cols to Process",#"New Col Names"})))
in
    #"Reorder Cols"

Custom function: Rename as per code comment

//Rename Query "fnRecordTypes"

(fieldNames as list, fieldTypes as list)=>
   
let
    rowColumnTypes = List.Transform(fieldTypes, (t) => [Type = t, Optional = false]),
    rowType = Type.ForRecord(Record.FromList(rowColumnTypes, fieldNames),false)
in
   rowType

This results in the same output as my previous answer. Note that if you create the chart in Power BI desktop, you can easily sort the X-axis labels , even though your data is not.