Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have multiple sources of data in the below format (PDF) where the data is below the header:
I am trying to make it so it combines all of the data sources and transforms them into the below format:
I have managed to do this with one data source through transposing, unpivoting columns and stacking columns etc. but when there are multiple data sources, the method that worked for one data source doesn't work for multiple due to it being in the wrong order when I do the final step of pivoting the column in an attempt to get the desired output I showed above.
Anyone got any advice?
As Imke replied you should better provide some samples of your pdf source files.
Meanwhile this might help.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCkvMKU1VcEksSbUC8oAopCgxBUUgODUnR8G5tKgoNS+50kopVgesz1DfwlDfyMDIGKLIRN/IBIkbGuwCU4iCoGKOufmleSXFCgGJlYlJOakKSZVAZlFJpYIT1E6cChyhCpxKK7G4ycAAImtuDqHdnQLwOSMI4cmAovyU0uQSKM85Py8tsyhXIbgksaS0GGG+nhHUArfyFBSVqSlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
List = List.Combine(Table.ToColumns(Source)),
Headers = {"Value Date:","Trade Date:", "Sell Currency:", "Amounts Payable by Party B:", "Rate:", "Buy Currency:", "Amounts Payable by Party A:" },
Indices = List.Transform (Headers, each List.PositionOf ( List, _)+1),
Values = List.Transform (Indices, each List{_}),
Promoted = Table.PromoteHeaders ( Table.FromRows({Headers,Values}) )
in
Promoted
Hi @Faz ,
hard to tell without seeing what you actually did.
Can you share your M-code from the advanced editor please?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries