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.
Hello guys,
How is everything?
Checking the below tables, I put the current dataset (but the provided is just a sample not the complete dataset) vs. the desired output.
What I aim to do is:
How I could achieve that? Any insights?
Solved! Go to Solution.
I would break this into 3 queries, then append all three.
First, I would make three new blank queries, named First, Breaks, and Meals. Suppose your main table is named FactTable.
For the First query, in the formula bar, you want to group the FactTable. I can't quite be 100% sure on the syntax because I'm not at my desk, but its:
=Table.Group(FactTable,//and group by Name and Date. Just use the GUI button, and be sure to select an aggregation -- All Rows. Name that column "Details".
Next, add a column to get each first row:
= Table.AddColumn(Source, "FirstRows", each [Details]{0})
You can now delete your original columns and then expand the Details column.
For the Breaks query, in the formula bar, type:
= Table.SelectRows(FactTable, each [Code] = "Break")
Then group this table by Name and Date, also using the All Rows aggregation, also named "Details". But for the multiple breaks per
day, we need to add an index column to each nested table:
= Table.TransformColumns(Source, {{"Details", each Table.AddIndexColumn([Details], "Index" 1,1, type number)}})
Now you can get rid of your original columns and then expand your Drtails column. Now you can transform the Code column:
= Table.TransformColumns(#"Removed Columns", {{"Code", each Text.Insert([Code], 0, if [Index] = 1 then "First " else if [Index] = 2 then "Second " else if [Index] = 3 then "Third " else "Fourth ")}})
Now you can remove the Index Column.
The Lunch query is straightforward:
= Table.SelectRows(FactTable, each [Code] = "Meal")
Now that you have all three tables, double check to make sure that the column names and types are the same, and then you can make your final query. Add a blank query, and in the Advanced Editor, type:
let
EmptyTable = Table.FromRows({{null, null, null}}, type table [Name = text, Code = text, Date = date]),
FirstTable = if Table.IsEmpty(First) then EmptyTable else First,
SecondTable = if Table.IsEmpty(Breaks) then EmptyTable else Breaks,
ThirdTable = if Table.IsEmpty(Meals) then EmptyTable else Meals,
FinalTable = Table.Combine({FirstTable, SecondTable, ThirdTable})
in FinalTable
That should do it!
--Nate
I would break this into 3 queries, then append all three.
First, I would make three new blank queries, named First, Breaks, and Meals. Suppose your main table is named FactTable.
For the First query, in the formula bar, you want to group the FactTable. I can't quite be 100% sure on the syntax because I'm not at my desk, but its:
=Table.Group(FactTable,//and group by Name and Date. Just use the GUI button, and be sure to select an aggregation -- All Rows. Name that column "Details".
Next, add a column to get each first row:
= Table.AddColumn(Source, "FirstRows", each [Details]{0})
You can now delete your original columns and then expand the Details column.
For the Breaks query, in the formula bar, type:
= Table.SelectRows(FactTable, each [Code] = "Break")
Then group this table by Name and Date, also using the All Rows aggregation, also named "Details". But for the multiple breaks per
day, we need to add an index column to each nested table:
= Table.TransformColumns(Source, {{"Details", each Table.AddIndexColumn([Details], "Index" 1,1, type number)}})
Now you can get rid of your original columns and then expand your Drtails column. Now you can transform the Code column:
= Table.TransformColumns(#"Removed Columns", {{"Code", each Text.Insert([Code], 0, if [Index] = 1 then "First " else if [Index] = 2 then "Second " else if [Index] = 3 then "Third " else "Fourth ")}})
Now you can remove the Index Column.
The Lunch query is straightforward:
= Table.SelectRows(FactTable, each [Code] = "Meal")
Now that you have all three tables, double check to make sure that the column names and types are the same, and then you can make your final query. Add a blank query, and in the Advanced Editor, type:
let
EmptyTable = Table.FromRows({{null, null, null}}, type table [Name = text, Code = text, Date = date]),
FirstTable = if Table.IsEmpty(First) then EmptyTable else First,
SecondTable = if Table.IsEmpty(Breaks) then EmptyTable else Breaks,
ThirdTable = if Table.IsEmpty(Meals) then EmptyTable else Meals,
FinalTable = Table.Combine({FirstTable, SecondTable, ThirdTable})
in FinalTable
That should do it!
--Nate
You gave me fantastic insights of the solution sequence, yes some of the provided scripts needed some search to correct them, but this is even better and more interesting to learn the right code by searching here and there, what I needed the most was directions of steps of what to do, which you have provided perfectly, you are truly good instructor, thanks a million.
Thank you very much!!
NameCodeDate
First Person | Login | 8/20/2021 |
First Person | First Break | 8/20/2021 |
Second Person | System | 8/24/2021 |
Second Person | First Break | 8/24/2021 |
Second Person | Meal | 8/24/2021 |
Second Person | Second Break | 8/24/2021 |
NameCodeDate
First Person | Login | 8/20/2021 |
First Person | Work | 8/20/2021 |
First Person | Break | 8/20/2021 |
First Person | Work | 8/20/2021 |
First Person | Not Available | 8/20/2021 |
Second Person | System | 8/24/2021 |
Second Person | Work | 8/24/2021 |
Second Person | Break | 8/24/2021 |
Second Person | Work | 8/24/2021 |
Second Person | Meal | 8/24/2021 |
Second Person | System | 8/24/2021 |
Second Person | Work | 8/24/2021 |
Second Person | Break | 8/24/2021 |
Second Person | Work | 8/24/2021 |