Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
Easier to explain in pictures, so:
I have grouped the data by user and what I would like to do now is to duplicate the "DateAndTime" column in each user table("Custom" column) AND also raise it by one row.. (End result is to subtract DateAndTime from the duplicated raised row to see time difference)
I'm trying to follow https://www.youtube.com/watch?v=90EYX7pzVlE but since my data is grouped I'm not sure about how to proceed..
Any help would be greatly appreciated
Solved! Go to Solution.
try this way:
let
...
...
breakTab=(tab as table) =>
let
timeDiff=List.Accumulate(List.Numbers(1,Table.RowCount(tab)-1), {},(s,c)=>s&{tab[Time]{c}-tab[Time]{c-1}})
in Table.FromColumns({timeDiff},{"timeDiff"}),
diff= Table.Group(yourTab, {"Group"}, {{"Diff", each breakTab(_), type table}}),
te = Table.ExpandTableColumn(diff, "Diff", {"timeDiff"}, {"timeDiff"})
in
te
Thank you both @Anonymous @Mariusz , your solutions worked well 🙂 Have a good day and thanks!
try this way:
let
...
...
breakTab=(tab as table) =>
let
timeDiff=List.Accumulate(List.Numbers(1,Table.RowCount(tab)-1), {},(s,c)=>s&{tab[Time]{c}-tab[Time]{c-1}})
in Table.FromColumns({timeDiff},{"timeDiff"}),
diff= Table.Group(yourTab, {"Group"}, {{"Diff", each breakTab(_), type table}}),
te = Table.ExpandTableColumn(diff, "Diff", {"timeDiff"}, {"timeDiff"})
in
te
Hi @eeew00
You can try this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1jcw0zcyMDJQMLC0MjBTitXBkDA0sDKESBih6bCA6UCTMDQE64gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, DateTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", Int64.Type}, {"DateTime", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"User"}, {{"rows", each Table.AddIndexColumn( _, "Index" ), type table [User=number, DateTime=datetime, Index=number]}}),
#"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"DateTime", "Index"}, {"DateTime", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded rows", "Index+1", each [Index]+1, type number ),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index+1", "User"}, #"Added Custom", {"Index", "User"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"DateTime"}, {"Added Custom.DateTime"})
in
#"Expanded Added Custom"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.