Combine multiple fields in Two Rows to Single Row, separate columns based on another column

[Anonymous]

Dear all,

I am new to BI, and really struggling to figre this one out.

I have a table listing goods movements in a warehouse  with about 60 columns. Each row should represent an activity start to finish. However, most activities get reported across 2 rows instead of one, so date/time columns (among others) can be both start and finish data.

Is it possible to move certain columns ( about 20) from the second row of data up as additional fields on the first row, similar to the below. The Order Column value recurring would determine if the move should happen.

Any help would be appreciated!

Thanks

[dax · Accepted solution]

Hi @Anonymous , 

You could try below M code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc87CsMwDADQu3gORJKdDtrclmQrhY7BQxJnLYHeH6oPdAgdDLIshJ+E5znk0AUEQLmyJgKC/tIjWJ+JJKQ85EgsSyhdC4qDg+NdJa+rsasznZfpzIghMWj50WPNbTN3cxfVxT8OGYffLolaG5l9zbfpgH03d3eX1KWzi4yyTj/9Qsfj2KSIrZR4PvTtNIVSvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, Product = _t, ID = _t, Date = _t, Time = _t, Source = _t, Destination = _t, Activity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Product", Int64.Type}, {"ID", type text}, {"Date", type date}, {"Time", type time}, {"Source", type text}, {"Destination", type text}, {"Activity", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Order"}, {{"all", each _, type table [Order=text, Product=number, ID=text, Date=date, Time=time, Source=text, Destination=text, Activity=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([all],"Custom", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Product", "ID", "Date", "Time", "Source", "Destination", "Activity", "Custom"}, {"Product", "ID", "Date", "Time", "Source", "Destination", "Activity", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.1] = 2)),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Order"}, #"Filtered Rows", {"Order"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Order", "Product", "ID", "Date", "Time", "Source", "Destination", "Activity"}, {"Order.1", "Product.1", "ID.1", "Date.1", "Time.1", "Source.1", "Destination.1", "Activity.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Filtered Rows", each ([Custom.1] = 1))
in
    #"Filtered Rows1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax 

Thank you for this solution. wrks a treat for the sample data.

I am still trying to emplement this step by step so that I learn and understand  what the M code is doing.

I do have one question - With #Expanded Custom, can I control the columns which get carried, and do I apply this to Custom or Custom.1. I cannot test at the moment as I am having service issues.

Thanks again,

Barry