New and Need Help - Alternating Rows

[mkastler]

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? 🥺

[mdaatifraza5556 · Accepted solution]

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.

[mkastler]

Thank 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!!

[ThxAlot]

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
    Transformed

[v-sathmakuri]

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!!

[CR17]

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!

[AlienSx]

((r) => #table(r{0} & r{2}, {r{1} & r{3}}))(List.Buffer(Table.ToList(your_table, each _)))

[ronrsnfld · Accepted solution]

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:

[CR17]

Yes

[mdaatifraza5556 · Accepted solution]

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.

[parry2k]

@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.

[ibj295]

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:

• LinkedIn

• Website