Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello Everybody,
I an trying to automate a PDF to Excel conversion using power query, I have not been able to accomplish this at level of precision I wanted to and need some help.
This is the snap of read query :
Since the pdf splits into two pages at row 12 and 13 but remains in single table, the whole table is read, but is not returning me a complete dataset. I want to merge the row which has null value in "SL. No" column with the row above.
I have tried to add a condition to the FillUp function, but it only seems to apply merge to a select column.
This is the section of one pdf when the table splits
Any help is greatly appreciated.
Thank you for the time !
Solved! Go to Solution.
Hi @ArulBhargavR ,
You can apply the following codes in Advanced Editor to achieve it, please find the details in the attachment.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY5dC4IwFIb/ymHXSW4t6HbOySI/hvODcCJBQVLMC/3/NKkrBW+CA+eF94XnaRqECdoheiI+8X3fRUK1op3s7b33ohjSYXpAMAwvuPSTR6enm4hEgT5DBYUGeQ2NZYkQqSpz1+1Ru2uQC+7CnEWF+1ykRc5iY2uWy6zUwljNZZbF82ye48Pa4bjtUP0k6pWEsVu8rx+mSyBXuOOBFgsY4HEC/r6N43/M9gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SI .No" = _t, #"HSN Number" = _t, #"Item Description" = _t, #"Shipment Branch" = _t, #"Contact Number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SI .No", Int64.Type}, {"HSN Number", type text}, {"Item Description", type text}, {"Shipment Branch", type text}, {"Contact Number", type text}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"SI .No"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"SI .No"}, {{"Details", each _, type table [SI .No=nullable number, HSN Number=nullable number, Item Description=nullable text, Shipment Branch=nullable text, Contact Number=nullable text]}}),
#"Aggregated data" = Table.AggregateTableColumn(#"Grouped Rows", "Details", {{"HSN Number", (x) => Text.Combine(x, "#(lf)"), "HSN Number"}, {"Item Description",(x) => Text.Combine(x, "#(lf)"), "Item Description"}, {"Shipment Branch",(x) => Text.Combine(x, "#(lf)"), "Shipment Branch"}, {"Contact Number",(x) => Text.Combine(x, "#(lf)"), "Contact Number"}} )
in
#"Aggregated data"
Best Regards
Hi @ArulBhargavR ,
You can apply the following codes in Advanced Editor to achieve it, please find the details in the attachment.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY5dC4IwFIb/ymHXSW4t6HbOySI/hvODcCJBQVLMC/3/NKkrBW+CA+eF94XnaRqECdoheiI+8X3fRUK1op3s7b33ohjSYXpAMAwvuPSTR6enm4hEgT5DBYUGeQ2NZYkQqSpz1+1Ru2uQC+7CnEWF+1ykRc5iY2uWy6zUwljNZZbF82ye48Pa4bjtUP0k6pWEsVu8rx+mSyBXuOOBFgsY4HEC/r6N43/M9gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SI .No" = _t, #"HSN Number" = _t, #"Item Description" = _t, #"Shipment Branch" = _t, #"Contact Number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SI .No", Int64.Type}, {"HSN Number", type text}, {"Item Description", type text}, {"Shipment Branch", type text}, {"Contact Number", type text}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"SI .No"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"SI .No"}, {{"Details", each _, type table [SI .No=nullable number, HSN Number=nullable number, Item Description=nullable text, Shipment Branch=nullable text, Contact Number=nullable text]}}),
#"Aggregated data" = Table.AggregateTableColumn(#"Grouped Rows", "Details", {{"HSN Number", (x) => Text.Combine(x, "#(lf)"), "HSN Number"}, {"Item Description",(x) => Text.Combine(x, "#(lf)"), "Item Description"}, {"Shipment Branch",(x) => Text.Combine(x, "#(lf)"), "Shipment Branch"}, {"Contact Number",(x) => Text.Combine(x, "#(lf)"), "Contact Number"}} )
in
#"Aggregated data"
Best Regards
Can you provide a sample pdf and the power query code you have tried so far?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
21 | |
6 | |
3 | |
3 | |
3 |