Forum Discussion
Need help for generating raw wise difference from column using DAX or power query
- 1 year ago
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
Here is M-Code that will calculate the Difference columns, and arrange it in the order you show.
Paste the code below into the Advanced Editor to see how it works, then adapt it to your actual data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBCgIxDAXQq0jXIzRJk5+ew+UwOxHduNC5P5bWkakd6CLQx+cn8xz0zOVF1jAFeq+ny/1xW8vMlhJpLpNld1BYpoptw/y87jGcvOJM6gOW1x4rCdAwp7xhHCcrXLVhGAbcJxtpxYgx8q+GHy9oYoKGiWVIHjC+WMr3f3Lf2eDiDaukAfedQUax4XLEDefjGhCoNeylz7J8AA==", 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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Date", type date}, {"Shift", type text}, {"Machine A", Int64.Type}, {"Machine B", Int64.Type}}),
#"Add Differences" =
[a=List.Skip(#"Changed Type"[Machine A]),
b=List.Skip(#"Changed Type"[Machine B]),
c=List.Zip({a,#"Changed Type"[Machine A]}),
d=List.Zip({b,#"Changed Type"[Machine B]}),
e=List.Transform(c, each _{0} - _{1}),
f=List.Transform(d, each _{0} - _{1}),
g=Table.FromColumns(
Table.ToColumns(#"Changed Type")
& {e,f},
type table[Date=date, Shift=text,Machine A=Int64.Type,Machine B=Int64.Type,
Difference A=Int64.Type, Difference B=Int64.Type])][g],
#"Reordered Columns" = Table.ReorderColumns(#"Add Differences",
{"Date", "Shift", "Machine A", "Difference A", "Machine B", "Difference B"})
in
#"Reordered Columns"
Results from your data:
Just by formatting the Line Chart visual, you can get this:
I don't know how to rotate the x-axis labels on this visual except by making the visual more narrow. I merely used Data and Shift for the x-axis and selected to concatenate the labels.