Merge rows when an Index field has null value

[ArulBhargavR]

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 !

[v-yiruan-msft · Accepted 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

[lbendlin]

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