Consolidate multiple rows into one for a report

[breezett93]

Included is a picture of the result of a query. I need the non-zero value from each row saved and merged into just one row (Whoever designed the table I'm working with didn't plan ahead very well). There is more data beyond the image in each row, but they are all identical; because I'm really dealing with just one item.

I'm getting a blank report, and I think it's because it doesn't know which row to pick (should be the top one); so I'm hoping my consolidation solution will work.

Any thoughts on the best way to combine each row, then delete the remaining five? If I had a second item, then twelve rows would be generated; so some form of looping is required as well.



Thanks

[pbaldy]

Start a new query and add all those fields. Click on the Totals icon in the ribbon. Change Group By to Max for all the fields except the first one, and see if that gets the result you want.

[alansidman]

This is an easy task using Power Query. Here is the Mcode. I used an excel sheet to recreate your situation, but importing a query into PQ can achieve the same results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ordid", Int64.Type}, {"ETop", type number}, {"ELeftOS", type number}, {"ELeftIS", type number}, {"ERightOS", type number}, {"ERightIS", type number}, {"Ebottom", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,null,Replacer.ReplaceValue,{"ETop", "ELeftOS", "ELeftIS", "ERightOS", "ERightIS", "Ebottom"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"ETop", "ELeftOS", "ELeftIS", "ERightOS", "ERightIS", "Ebottom"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Filled Up",5)
in
    #"Removed Bottom Rows"

[breezett93]

That worked. Thank you!

[pbaldy]

Whoever helped was happy to do so.

[alansidman]

Concur with Paul.