cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcoGamba
New Member

New Table/Reshaped Table grouped by date and keeping non-blank values for each column

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:

MarcoGamba_0-1656834709683.png

 

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

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @MarcoGamba 

 

Here a solution in Power Query:

Before:

tomfox_0-1656835625235.png

 

After:

tomfox_1-1656835662075.png

 

 

Here the solution in M that you can paste in advanced editor:

tomfox_2-1656835735056.png

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? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

Hi @MarcoGamba 

 

Here a solution in Power Query:

Before:

tomfox_0-1656835625235.png

 

After:

tomfox_1-1656835662075.png

 

 

Here the solution in M that you can paste in advanced editor:

tomfox_2-1656835735056.png

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? Mark my post as a solution!

Proud to be a Super User!




Hello Tom,

thank you so much for the super fast help!!

It definitely works!

You saved my WE 🙂

 

Regards,

Marco

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors