SOLVED

Turning alternate rows into columns - Power Query/M Function

Brass Contributor

So I have some messy data coming from pdf invoices which I have 95% cleaned how I want, but I cannot work out the last step.

 

I am copying the data into a table with ctrl a/c/v and it spits it into a single column.

This is fine, using conditional columns etc in PQ I can get data to look like table on left in below pic.

davidmaddock54_0-1640743211860.png

I want it to end up like table on right. Playing with transpose/unpivot/pivot/split doesn't seem to get me the results I want.

 

I'd strongly prefer a solution in PQ, but if I have to use regular formulas I can work it out. This will be a monthly task, and invoice size will vary.

 

Any thoughts? I feel like I've missed something obvious.

 

 

7 Replies
best response confirmed by davidmaddock54 (Brass Contributor)
Solution

@davidmaddock54 The attached workbook contains a working query that does what you asked for. I trust you can get it to work in your own schedule.

 

It involves adding an Index column from zero. Then a Modulo of 2 on the Index, creating a column with alternating zeroes and ones. Pivot the Modulo column without aggregation of Column1 and do some cleaning up.

Thanks so much. I get an error when I open the query editor, but I think I can follow the steps in the formula bar. It certainly spits out what I want at the end.

Oh, scrap that, errors on work computer, but it shows on my own laptop. Handy. I can do some comparisons and make sure I go step by step.

Thanks again.
And it works. Set up now.

@davidmaddock54 Glad you could work it out!

@davidmaddock54 

One more variant for the collection

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Items  = Table.AlternateRows(Source,1,1,1),
    Values = Table.AlternateRows(Source,0,1,1),
    Result = Table.FromColumns( {Items[Column1], Values[Column1]}, {"Value", "Item"}  )

in
    Result
Thank you. More options and chances to work with M functions the better. There's a few more things I may need from this info, might be handy to have alternative options.
1 best response

Accepted Solutions
best response confirmed by davidmaddock54 (Brass Contributor)
Solution

@davidmaddock54 The attached workbook contains a working query that does what you asked for. I trust you can get it to work in your own schedule.

 

It involves adding an Index column from zero. Then a Modulo of 2 on the Index, creating a column with alternating zeroes and ones. Pivot the Modulo column without aggregation of Column1 and do some cleaning up.

View solution in original post