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

Here is the result using Power query.

1.Select the range

2.Select Column Parameter, Transform>Povit Column

3.Choose Pred to povit, Click Ok. Then Close and load

Best Regards,

Snow Lu

Answer 4

Hi Snow Lu

Thank you for your quick response.. I need to transform the whole table and create the 42 column names from the combination of values in B2 to B7 and the source column headings in C1 to I1. (plus the ID column at the start)... Is there a way to flatten the whole table (A1 to I7)..

Answer 5

Hi,

Share data in a format that can be pasted in an MS Excel file. For any one ID, please also show the expected result in another tab of the file.