Forum Discussion
New and Need Help - Alternating Rows
Hello! I'm just starting out with Power Query and need help solving this problem (and AI is running me in loops with nonsense solutions).
Here is the example table (BEFORE):
| PO Number | Name | BOL Number | ASN Number |
| 123456 | John Smith | 78910 | 12555 |
| Packing Slip Number | Date | To | From |
| 99999 | 5/6/2026 | New York | San Diego |
My hope is to get it to this:
| PO Number | Name | BOL Number | ASN Number | Packing Slip Number | Date | To | From |
| 123456 | John Smith | 78910 | 12555 | 99999 | 5/6/2026 | New York | San Diego |
I have over 500 pdfs that I'm pulling from (all the same format) and I need to consolidate this information into a summary table from each PDF. However, I'm stuck at the first part = how to unstack alternating rows.
Can anyone please help me? 🥺
Hi mkastler
Please use the below M Code and use it in by opening advance editor and paste this code there.let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBX8CvNTUotUtJR8kvMTQVSTv4+CDHHYD8YJ1YnWsnQyNjE1Awo7pWfkacQnJtZkgHkmFtYGhoAaUMjU1NTsLqAxOTszLx0heCczAKEYS6JJSALQvKBhFtRfi5YqSUIAAVM9c30jQyMQIb7pZYrROYXZQOZwYl5Ci6Zqen5SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
// First header/value pair
Header1 = Record.FieldValues(#"Changed Type"{0}),
Values1 = Record.FieldValues(#"Changed Type"{1}),// Second header/value pair
Header2 = Record.FieldValues(#"Changed Type"{2}),
Values2 = Record.FieldValues(#"Changed Type"{3}),// Combine headers and values
Headers = List.Transform(
List.Combine({Header1, Header2}),
each Text.From(_)
),
Values = List.Combine({Values1, Values2}),// Create one record
Result = Table.FromRecords({
Record.FromList(Values, Headers)
})
in
Result
************************************************************************************************
In the above code just fix the source path.If this answers your questions, kindly accept it as a solution and give kudos.
You will need to use the Data --> from PDF wizard to read in a pdf document and extract the table.
You can then use List.Accumulate to Data from Folder to create a list of all the relevant pdf's and combine them into separate rows to create your final table.
The code below assumes you are starting from a single table like this (note the column headers are default)
To convert a single table, the code below should suffice, converting the rows to a single row.
let //Read in your PDF Document and extract the relevant table //I cannot help you here since you don't provide a pdf document to try out //But you can use the "Get Data from ==>PDF" wizard in Power BI //to produce "theTable" and then reference that in the first step below. //Split the rows into a list of tables with two rows per table (a pair of rows) Pairs = Table.Split(theTable,2), //In each pair, promote the first row to Headers and convert to records #"Promote to Headers" = List.Transform(Pairs, each Table.ToRecords(Table.PromoteHeaders(_,[PromoteAllScalars=true, Culture="en-US"]))), //Combine the records and convert to table #"New Table" = Table.FromRecords({Record.Combine(List.Combine(#"Promote to Headers"))}) in #"New Table"The result:
10 Replies
mkastler based on your raw data input, I made the solution. Feel free to tweak as you see fit:
TableRaw is the raw data you provided
TablePO and TablePacking are created from raw data
FinalTable is the output that joins the PO and packing slip tables to get the PO and packing on the same row.
Hi mkastler
Please use the below M Code and use it in by opening advance editor and paste this code there.let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBX8CvNTUotUtJR8kvMTQVSTv4+CDHHYD8YJ1YnWsnQyNjE1Awo7pWfkacQnJtZkgHkmFtYGhoAaUMjU1NTsLqAxOTszLx0heCczAKEYS6JJSALQvKBhFtRfi5YqSUIAAVM9c30jQyMQIb7pZYrROYXZQOZwYl5Ci6Zqen5SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
// First header/value pair
Header1 = Record.FieldValues(#"Changed Type"{0}),
Values1 = Record.FieldValues(#"Changed Type"{1}),// Second header/value pair
Header2 = Record.FieldValues(#"Changed Type"{2}),
Values2 = Record.FieldValues(#"Changed Type"{3}),// Combine headers and values
Headers = List.Transform(
List.Combine({Header1, Header2}),
each Text.From(_)
),
Values = List.Combine({Values1, Values2}),// Create one record
Result = Table.FromRecords({
Record.FromList(Values, Headers)
})
in
Result
************************************************************************************************
In the above code just fix the source path.If this answers your questions, kindly accept it as a solution and give kudos.
Since all of your PDFs have the same structure, you can automate this in Power Query.
The basic approach is:
Add an Index column.
Create a Group column using Number.IntegerDivide([Index], 2) so every two rows belong to the same record.
Group the rows.
Combine the values from each pair of rows into a single row.
Expand the result into the final columns.
Here's an example M query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],// Add Index
AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),// Group every two rows
AddGroup = Table.AddColumn(AddIndex, "Group", each Number.IntegerDivide([Index], 2), Int64.Type),GroupRows = Table.Group(AddGroup, {"Group"},
{{"Data", each _, type table}}),CombineRows = Table.AddColumn(GroupRows, "Merged", each
let
t = [Data],
r1 = Record.ToList(t{0}),
r2 = Record.ToList(t{1})
in
List.Combine({List.FirstN(r1,4), List.FirstN(r2,4)})
),Result = Table.FromRows(
CombineRows[Merged],
{
"PO Number","Name","BOL Number","ASN Number",
"Packing Slip Number","Date","To","From"
}
)
in
Result
This will transform:PO Number | Name | BOL Number | ASN Number
123456 | John Smith | 78910 | 12555
99999 | 5/6/2026 | New York | San Diego
into:PO Number | Name | BOL Number | ASN Number | Packing Slip Number | Date | To | From
123456 | John Smith | 78910 | 12555 | 99999 | 5/6/2026 | New York | San Diego
Since you're processing 500+ PDFs with the same layout, you only need to apply this transformation once, and it will work for every file. If your PDF connector produces a slightly different structure, feel free to share a screenshot of the Power Query preview, and I can adjust the M code accordingly.If this helped solve your issue, please consider Accepting it as the Solution ✔️ and giving it a Like 👍. It helps others in the community find the answer more easily.
Thanks!
Connect with me:
Yes
You will need to use the Data --> from PDF wizard to read in a pdf document and extract the table.
You can then use List.Accumulate to Data from Folder to create a list of all the relevant pdf's and combine them into separate rows to create your final table.
The code below assumes you are starting from a single table like this (note the column headers are default)
To convert a single table, the code below should suffice, converting the rows to a single row.
let //Read in your PDF Document and extract the relevant table //I cannot help you here since you don't provide a pdf document to try out //But you can use the "Get Data from ==>PDF" wizard in Power BI //to produce "theTable" and then reference that in the first step below. //Split the rows into a list of tables with two rows per table (a pair of rows) Pairs = Table.Split(theTable,2), //In each pair, promote the first row to Headers and convert to records #"Promote to Headers" = List.Transform(Pairs, each Table.ToRecords(Table.PromoteHeaders(_,[PromoteAllScalars=true, Culture="en-US"]))), //Combine the records and convert to table #"New Table" = Table.FromRecords({Record.Combine(List.Combine(#"Promote to Headers"))}) in #"New Table"The result:
((r) => #table(r{0} & r{2}, {r{1} & r{3}}))(List.Buffer(Table.ToList(your_table, each _)))Hi mkastler ,
Thank you for reaching out to Fabric community.
Thank you AlienSx , ronrsnfld and mdaatifraza5556 for prompt response.Could you review the suggestion provided by , and and let us know if you have any additional questions, we are happy to address.
Thanks!!
Hi everyone,
Thank you so much for the prompt and detailed responses!
AlienSx - Your concise M code worked perfectly for adjusting the alternating rows in my table.
ronrsnfld - I really appreciate the detailed step-by-step breakdown and the advice on using the PDF wizard, it helped me understand the logic much better.
Thanks again to the community for the amazing support!
For fun only, one-liner to handle it,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBX8CvNTUotUtJR8kvMTQVSTv4+CDHHYD8YJ1YnWsnQyNjE1Awo7pWfkacQnJtZkgHkmFtYGhoAaUMjU1NTsLqAxOTszLx0heCczAKEYS6JJSALQvKBhFtRfu6hBWDFliAAFDLVN9M3MjACGe+XWq4QmV+UDWQGJ+YpuGSmpucrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), Transformed = Table.PromoteHeaders(Table.FromRows(List.Transform(List.Zip(List.Split(Table.ToRows(Source), 2)), List.Combine))) in TransformedThank you everyone for helping me~!
I realized that there was quite a mix of ways to solve this particular problem, so it was all very good to learn. I appreciate everyone who responded, thank you!!
