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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.