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
Hi Nasif_Azam & speedramps ,
Thank you for the prompt response. I have tried it and it worked well. Both of you have a similar kind of solution. May many thanks to both of you!
However, I want to know what if there are more than 25 columns and we need this difference for all these columns? It will be tedious to add formula and column for that type of data. So can you please suggest any alternate solution for multiple columns in terms of measure or something?
Thanks in Advance!
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.