Forum Discussion
Need help for generating raw wise difference from column using DAX or power query
Hi again
Please try ask the right question first time, because it is a bit annoying having to answer twice 😀😀😀
Click here to download a solution ftom one drive
This method will work for 25 machines or more
Please will you now click [thumbs up] and [accept solutoon button]. Thank you !
How it works ...
For the date correctly
Select the Date and Shift columns, then Transform> Unpiciot>Unpivot other columns
Sort by Machine, Date and Shift
Add an index column from 0 incrementing by 1
Get the machine and reading for the previous row
Change reading to numeric data types
and add a conditional columnRenove the unneeded columns and change the usage ro a numeric data type
Draw your graph
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.
Sort your data by Date & Shift.
Add an Index Column (Index).
Duplicate the table and shift the index (PrevIndex = Index + 1).
Merge on Index and PrevIndex.
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
AddDifferencesThis 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
Hi @Nasif_Azam ,
Thank you so much for guidance and quick response. (Just for your information - I don't know much about M code so ignor silly mistakes I did when I followed these steps)
What I have done first is - merge two tables as a new and then expand the remaining columns as shown in below M code.
let
Source = Table.NestedJoin(#"Raw Data", {"Index"}, #"Raw Data Prev", {"Index"}, "Raw Data Prev", JoinKind.LeftOuter),
#"Expanded Raw Data Prev" = Table.ExpandTableColumn(Source, "Raw Data Prev", {"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading", "Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading", "6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading", "Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading", "Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading", "8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)", "PDB - 4 Meter Reading", "W601 Assembly Meter Reading"}, {"Raw Data Prev.MAIN TR Power Reding ", "Raw Data Prev.Bus Trunk -1 Meter reading", "Raw Data Prev.Bus Trunk -6 Meter Reading", "Raw Data Prev.Bus Trunk -2 Meter Reading", "Raw Data Prev.Bus Trunk -4 Meter Reading", "Raw Data Prev.Bus Trunk -3 Meter Reading", "Raw Data Prev.6F2 Meter Reding", "Raw Data Prev.Bus Trunk -5 Meter Reading", "Raw Data Prev.Bus Trunk -9 Meter Reading", "Raw Data Prev.Bus Trunk -10 Meter Reading", "Raw Data Prev.Logistic Meter Reading", "Raw Data Prev.Fan FDB Meter Reading", "Raw Data Prev.Office + Canteen Meter Reading#(lf)", "Raw Data Prev.ELDB Meter Reading", "Raw Data Prev.Bus Trunk -7 Meter Reading", "Raw Data Prev.8F4 Reading", "Raw Data Prev.Main PDB Meter Reading", "Raw Data Prev.U321 PDB Meter Reading (J3-601)", "Raw Data Prev.PDB - 4 Meter Reading", "Raw Data Prev.W601 Assembly Meter Reading"})
in
#"Expanded Raw Data Prev"
After that, I have replace the above code with your code as you suggested and end up with the following error -
M code for this is -
let
columnsToCompare = {{"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading", "Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading", "6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading", "Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading", "Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading", "8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)", "PDB - 4 Meter Reading", "W601 Assembly Meter Reading"}, {"Raw Data Prev.MAIN TR Power Reding ", "Raw Data Prev.Bus Trunk -1 Meter reading", "Raw Data Prev.Bus Trunk -6 Meter Reading", "Raw Data Prev.Bus Trunk -2 Meter Reading", "Raw Data Prev.Bus Trunk -4 Meter Reading", "Raw Data Prev.Bus Trunk -3 Meter Reading", "Raw Data Prev.6F2 Meter Reding", "Raw Data Prev.Bus Trunk -5 Meter Reading", "Raw Data Prev.Bus Trunk -9 Meter Reading", "Raw Data Prev.Bus Trunk -10 Meter Reading", "Raw Data Prev.Logistic Meter Reading", "Raw Data Prev.Fan FDB Meter Reading", "Raw Data Prev.Office + Canteen Meter Reading#(lf)", "Raw Data Prev.ELDB Meter Reading", "Raw Data Prev.Bus Trunk -7 Meter Reading", "Raw Data Prev.8F4 Reading", "Raw Data Prev.Main PDB Meter Reading", "Raw Data Prev.U321 PDB Meter Reading (J3-601)", "Raw Data Prev.PDB - 4 Meter Reading", "Raw Data Prev.W601 Assembly Meter Reading"}},
AddDifferences = Table.TransformColumns(
Merge1,
List.Transform(columnsToCompare, each {
"Diff " & _,
each Record.Field(_, _) - Record.Field(_, "Prev." & _),
type number
})
)
in
AddDifferences
Can you please explain why I'm getting this error and suggest a solution?
Thanks in Advance!
Try this one:
let
// Step 1: Original & Previous table already merged
Source = Merge1, // your merged table// Step 2: List of columns you want to compare
columnsToCompare = {
"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading",
"Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading",
"6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading",
"Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading",
"Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading",
"8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)",
"PDB - 4 Meter Reading", "W601 Assembly Meter Reading"
},// Step 3: Add difference columns dynamically
AddDiffColumns = List.Accumulate(
columnsToCompare,
Source,
(state, colName) =>
Table.AddColumn(
state,
"Diff " & colName,
each try [colName] - Record.Field(_, "Raw Data Prev." & colName) otherwise null,
type number
)
)
in
AddDiffColumns