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
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