How to transform a multi-row excel table into single row

Question

Hi there

Ive spent some time searching on excel but could find an example of this situation.. I came across Power Query also, which looks quiet powerful, but that is a new tool to me that will take some time to pick up...

I have a table extracted from a PDF file which has multiple rows per ID (cols A to I) . I need to create a csv file with a single row per ID, with a column for each combination of ParameterName (Col B) and Column Headings (C to I)..

I realise I can do some transforming with concatenate formulas, but is there a better way?

There will be about 500 PDFs to extract (one per ID). The structure of the source table extracted will always be the same, so it would be great if I could automate the extractions from a folder with the pdfs, and then save the output in a separate csv file for each extracted table.....

The screen shot attached has a small sample of the output Im looking for... eg. columns A, B and C from the source table are transformed...

Best regards

David

Answer 1

The query below will transfer the table into the desired format. It's done solely by clicking in the user interface. No manual M-coding needed. Note that "Table1" refers to the data in the Source sheet.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],#"Replaced value" = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"pred", "LLN", "Pre-Best", "Pre-Z-Score", "Post-Best", "Post-Z-Score", "%Chg"}),#"Unpivoted other columns" = Table.UnpivotOtherColumns(#"Replaced value", {"ID", "ParameterName"}, "Attribute", "Value"),#"Added index" = Table.AddIndexColumn(#"Unpivoted other columns", "Index", 0, 1, Int64.Type),#"Inserted modulo" = Table.AddColumn(#"Added index", "Modulo", each Number.Mod([Index], 7), Int64.Type),#"Sorted rows" = Table.Sort(#"Inserted modulo", {{"Modulo", Order.Ascending}, {"Index", Order.Ascending}}),#"Inserted merged column" = Table.AddColumn(#"Sorted rows", "Merged", each Text.Combine({[Attribute], Text.From([ParameterName])}, "_"), type text),#"Choose columns" = Table.SelectColumns(#"Inserted merged column", {"ID", "Value", "Merged"}),#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Choose columns", {{"Merged", type text}}), List.Distinct(Table.TransformColumnTypes(#"Choose columns", {{"Merged", type text}})[Merged]), "Merged", "Value")in#"Pivoted column"

Answer 2

Hi,

You may download my solution workbook from here. Add data by rows/columns to the "Source" worksheet and click on Data > Refresh All. The updated result will be available on the "Query Editor solution" worksheet.

Hope this helps.

Answer 3

Hi Ashish..

Thank you for looking at this.... I have a shared a sample xslx file on onedrive

tableExample.xlsx

best regards

David

Answer 4

Thanks Ashish.. this is great!

Answer 5

Thanks Riny... This worked too. I was able to just paste it into advanced editor ....