• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query Help - Merge null rows

Shaz_Mthembu

New Member
Hi All

Please help, after loading my pdf statement into power query, my part description field breaks into two rows. (one on first row and another on second row as a null value)

I need a way to merge my null row to have full description on one row.
 

Attachments

  • Sample File.xlsx
    23.7 KB · Views: 1
Obviously, we have no access to C:\Users\27833\Downloads\Donaldson PDf\ so we can't add to the queries and refresh them, so I've taken your existing results table and processed that. See the query Donaldson_PDfNew which uses the fnMergeRows function.
In a part of the function I've used the ¬ character to delimit the merged values because I wasn't sure whether you'd like no delimiter, a space delimiter, or something else. This way you can see where the delimiter has been used and you can go into the function to replace it with what you like.
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description],"¬")), can become
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description]," ")),
or with no delimiter:
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description])),

You should probably be able to use fnMergeRows directly in your own query.
 

Attachments

  • Chandoo56653Sample File.xlsx
    28.7 KB · Views: 4
Obviously, we have no access to C:\Users\27833\Downloads\Donaldson PDf\ so we can't add to the queries and refresh them, so I've taken your existing results table and processed that. See the query Donaldson_PDfNew which uses the fnMergeRows function.
In a part of the function I've used the ¬ character to delimit the merged values because I wasn't sure whether you'd like no delimiter, a space delimiter, or something else. This way you can see where the delimiter has been used and you can go into the function to replace it with what you like.
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description],"¬")), can become
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description]," ")),
or with no delimiter:
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description])),

You should probably be able to use fnMergeRows directly in your own query.
THANK YOU VERY MUCH AND REALLY GRATEFULL.
 
Obviously, we have no access to C:\Users\27833\Downloads\Donaldson PDf\ so we can't add to the queries and refresh them, so I've taken your existing results table and processed that. See the query Donaldson_PDfNew which uses the fnMergeRows function.
In a part of the function I've used the ¬ character to delimit the merged values because I wasn't sure whether you'd like no delimiter, a space delimiter, or something else. This way you can see where the delimiter has been used and you can go into the function to replace it with what you like.
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description],"¬")), can become
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description]," ")),
or with no delimiter:
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Source[Part Description])),

You should probably be able to use fnMergeRows directly in your own query.
Hi

Please one more thing if you do n0t mind., can you list the steps for me as what to do as step 1 and step 2 etc... my PQ skills are very limited.

Please help me understand what

Step 1 - Fill down (got that)

Step 2 - What to click and paste your formula?- (not sure here)

Step 3 - Filtered rows

Step 4 - not sure

Please excuse my limited skills
 
That's a big ask. It would take me all morning to go through the steps. There is some manual editing of the M-code.
The most obscure bit will be how the function fnMergeRows was put together.
In the attached, I've duplicated that function (fnMergeRows (2)) and turned it back into a normal query (go into Advanced Editor to see the differences) which will allow you to see the steps within it. The Added Custom step within it is the only step which has been manually put together, the others were created using the user interface.
I've also made a small change to the Grouped Rows step of the Donaldson_PDfNew query to allow the cogwheel to show and be clicked so you can see how the grouping was done.
1712910362908.png
 

Attachments

  • Chandoo56653Sample File2.xlsx
    29.3 KB · Views: 0
Back
Top