Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Folks,
I have a table recording the start and end time of each production shift every day. Given the system used to have the operators logging the time, the table is generated in a kind of "diagonal" way: for each day there are 4 lines (Start/End of the 2 shifts), but with only 1 non-blank column per day:
I need to reshape a table (or create a new one) with only one line per day and with the columns filled accordingly. The table can be generated either in DAX or in PowerQuery (either would work for the report).
I've tried with summarize and firstnonblank but no success so far 😞
Any hint?
Thanks for the support!
Regards,
Marco
Solved! Go to Solution.
Hi @MarcoGamba
Here a solution in Power Query:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5BCgAhCEDRu7QOxowZpatE97/GWLkpC8GFwcN+rQG+RwYBMUR5lAwFQFadFjcik1Z1Il2xEFUX0n/MemsQ8lvItKR3ayG/xZLZMm8Nwn4L+y3st1iytLQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_ShiftAdjust = _t, #"Day Start" = _t, #"Day End" = _t, #"Night Start" = _t, #"Night End" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_ShiftAdjust", type date}, {"Day Start", type time}, {"Day End", type time}, {"Night Start", type time}, {"Night End", type time}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date_ShiftAdjust"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @MarcoGamba
Here a solution in Power Query:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5BCgAhCEDRu7QOxowZpatE97/GWLkpC8GFwcN+rQG+RwYBMUR5lAwFQFadFjcik1Z1Il2xEFUX0n/MemsQ8lvItKR3ayG/xZLZMm8Nwn4L+y3st1iytLQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_ShiftAdjust = _t, #"Day Start" = _t, #"Day End" = _t, #"Night Start" = _t, #"Night End" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_ShiftAdjust", type date}, {"Day Start", type time}, {"Day End", type time}, {"Night Start", type time}, {"Night End", type time}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date_ShiftAdjust"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hello Tom,
thank you so much for the super fast help!!
It definitely works!
You saved my WE 🙂
Regards,
Marco
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |