topic Merge rows when an Index field has null value in Developer

Merge rows when an Index field has null value

ArulBhargavR — Fri, 16 Feb 2024 07:05:43 GMT

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 !

Re: Merge rows when an Index field has null value

lbendlin — Sat, 17 Feb 2024 21:42:35 GMT

Can you provide a sample pdf and the power query code you have tried so far?

Re: Merge rows when an Index field has null value

Anonymous — Mon, 19 Feb 2024 07:40:59 GMT

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