Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Experts
I am trying to work out the previous Value. in my data set. Previous Period is easy. [period]-1 in M. I would like to do the Previous Value formula in M
Here is my Table with expected result
Product | Date | Period | Previous Period | Value | Previous Value |
0 of 9% | 01/01/2021 | 0 | -1 | 202 | 0 |
0 of 9% | 01/01/2021 | 1 | 0 | 192 | 202 |
Solved! Go to Solution.
Hi @Anonymous
here is my solution:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRSitWJVjIzBVPGJhDa0ARMWUCFjczNINJKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]-1 < 0 then null else List.Range(#"Added Index"[Value], [Index]-1, 1)),
#"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Previous Value"}})
in
#"Renamed Columns"
Take a look at:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @Anonymous
here is my solution:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRSitWJVjIzBVPGJhDa0ARMWUCFjczNINJKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]-1 < 0 then null else List.Range(#"Added Index"[Value], [Index]-1, 1)),
#"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Previous Value"}})
in
#"Renamed Columns"
Take a look at:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@Anonymous , Lookup in power query?
there are few articles around it
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
Lookup is not going to work here Amit...
User | Count |
---|---|
80 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |