Dec 28 2021 06:03 PM
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.
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.
Dec 28 2021 08:44 PM
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.
Dec 28 2021 09:16 PM
Dec 28 2021 09:20 PM
Dec 28 2021 09:33 PM
Dec 28 2021 09:59 PM
@davidmaddock54 Glad you could work it out!
Dec 29 2021 12:03 AM
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
Dec 29 2021 01:09 AM
Dec 28 2021 08:44 PM
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.