Forum Discussion
Combine data from multiple rows into a single row
Hi Matt,
Attached is the sample with Power Query. That's done not optimal way to avoid coding and do all from UI, just to demonstrate the approach.
In brief, we unpivot other than SampleID columns, remove duplicates (as Var6, Var7), in second query Columns generate list of columns to be sure empty ones will be included (Var4, Var5), append that query to first one and pivot on attribute finally. Not sure if you need empty Row# column in final result, could be added.
Please see attached.
Hi Matt,
Attached is the sample with Power Query. That's done not optimal way to avoid coding and do all from UI, just to demonstrate the approach.
In brief, we unpivot other than SampleID columns, remove duplicates (as Var6, Var7), in second query Columns generate list of columns to be sure empty ones will be included (Var4, Var5), append that query to first one and pivot on attribute finally. Not sure if you need empty Row# column in final result, could be added.
Please see attached.
Sergei,
Thanks so much for your response. I think I understand what you did there, but I'm not sure if I can re-created it with my main spreadsheet of data. I'll give it a try, and I may ask for more guidance if you're willing to help.
- Matt
Matt,
Sure, will be glad to help. The only do not promise immediate answers.
- Hi Sergei,
I have a PQ merge that is spitting our double rows (62 rather than 31) because the source files include all columns the same but some cells of data reflect "---".
Would this method be the most efficient to use on larger datasets? My file has several columns.
