Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ArulBhargavR
Regular Visitor

Merge rows when an Index field has null value

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 :

 

ArulBhargavR_3-1708066935354.png

 

 

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

ArulBhargavR_1-1708066572539.png

 

Any help is greatly appreciated. 

 

Thank you for the time !

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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"

vyiruanmsft_0-1708328339655.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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"

vyiruanmsft_0-1708328339655.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!