Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
Need help rewriting formula dax in the language m - add a custom column
my formula: Previous = LOOKUPVALUE('445'[datetime],'445'[Rank],'445'[Rank]-1)
goal: append two queries (with the participation of the column)
similar theme unanswered:
http://community.powerbi.com/t5/Desktop/How-to-append-queries-to-calculated-columns/m-p/40520#U40520
It is also open to other solutions
Thank you
Solved! Go to Solution.
Hi @BI2B,
In your scenario, you can use UI to generate the backend M query.
1. Add a index column from 0.
2. Add a custom column like below:
=AddInd[datetime]{[Index]-1}
3. Replace the error with null value.
4. The backend M query like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1NNI1VNJRAmMDpVgdhLARUAiMUYWNgUJgjCpsAhQCY6BwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetime = _t, Rank = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type date}, {"Rank", Int64.Type}, {"Amount", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}), AddInd = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Custom" = Table.AddColumn(AddInd, "Custom", each AddInd[datetime]{[Index]-1}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}) in #"Replaced Errors"
Reference:
Absolute and Relative References in Power Query: R1C1 Excel-style approach
How can I do calculation based on next/prior row in Power Query?
Best Regards,
Qiuyun Yu
Hi @BI2B,
In your scenario, you can use UI to generate the backend M query.
1. Add a index column from 0.
2. Add a custom column like below:
=AddInd[datetime]{[Index]-1}
3. Replace the error with null value.
4. The backend M query like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1NNI1VNJRAmMDpVgdhLARUAiMUYWNgUJgjCpsAhQCY6BwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetime = _t, Rank = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type date}, {"Rank", Int64.Type}, {"Amount", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}), AddInd = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Custom" = Table.AddColumn(AddInd, "Custom", each AddInd[datetime]{[Index]-1}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}) in #"Replaced Errors"
Reference:
Absolute and Relative References in Power Query: R1C1 Excel-style approach
How can I do calculation based on next/prior row in Power Query?
Best Regards,
Qiuyun Yu
Hi Qiuyun,
Thank you very much for your help and solution
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |