Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a daily data interval table and need assistance with MCode, a measure or custom column to assign "On Site" or "Off Site" based on criteria....where total Work time = total Other time for the entire day, assign "Off Site" else "On Site" and have the assignment for the day assigned to each interval for the individual.
Solved! Go to Solution.
Hello, did it in a lot of steps, hope it helps.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Full Name", "Store", "Work", "Other"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Date", "Full Name", "Store"}, {{"Sum Work", each List.Sum([Work]), type number}, {"Sum Other", each List.Sum([Other]), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Location", each if [Sum Work] = [Sum Other] then "Off Site" else "On Site"),
Custom1 = Table.NestedJoin(#"Changed Type",{"Date", "Full Name", "Store"},#"Added Conditional Column",{"Date", "Full Name", "Store"},"Custom"),
#"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Location"}, {"Location"})
in
#"Expanded Custom"
Work, thank you! Had to adjust as the store could be 1 - 3 stores, so I removed "Store" as and where applicable. Thanks Again!
Hello, did it in a lot of steps, hope it helps.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Full Name", "Store", "Work", "Other"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Date", "Full Name", "Store"}, {{"Sum Work", each List.Sum([Work]), type number}, {"Sum Other", each List.Sum([Other]), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Location", each if [Sum Work] = [Sum Other] then "Off Site" else "On Site"),
Custom1 = Table.NestedJoin(#"Changed Type",{"Date", "Full Name", "Store"},#"Added Conditional Column",{"Date", "Full Name", "Store"},"Custom"),
#"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Location"}, {"Location"})
in
#"Expanded Custom"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |