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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Searching for words in a table and creating a new column containing that line in Power Query

I imported  a pdf and it came in shifted all over the place like below. These pdfs have a lot of lines but each row only contains one "Number of legs" data line.

Have.PNG

 

 

 

 

 

Is there any way to search through the table for "Number of legs" and make a new column like below with all the number of legs data in one column?

Want.PNG

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

Sure. This is best done in Power Query.  I'm assuming when it says null, that is actually a text field that says "null" and not the programmatic null.  If those are actual null values, then you can remove line 4, where I replace the word "null" with an actual null.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQVC5SalFCvlpCjmp6cVWCsZKsTrRuCQNYfogalAljZANxaHGEKsarHaZwNXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3"}),
    #"Added NewColumn" = Table.AddColumn(#"Replaced Value", "NewColumn", each Text.Combine({[Column1],[Column2],[Column3]}))
in
    #"Added NewColumn"

This will work if the entire import is just more of what you showed.  If there's other data, it will replace all instances of "null" with null and concatenate the entire line. Since nulls are not strings, they disappear.

 

View solution in original post

2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

Sure. This is best done in Power Query.  I'm assuming when it says null, that is actually a text field that says "null" and not the programmatic null.  If those are actual null values, then you can remove line 4, where I replace the word "null" with an actual null.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQVC5SalFCvlpCjmp6cVWCsZKsTrRuCQNYfogalAljZANxaHGEKsarHaZwNXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3"}),
    #"Added NewColumn" = Table.AddColumn(#"Replaced Value", "NewColumn", each Text.Combine({[Column1],[Column2],[Column3]}))
in
    #"Added NewColumn"

This will work if the entire import is just more of what you showed.  If there's other data, it will replace all instances of "null" with null and concatenate the entire line. Since nulls are not strings, they disappear.

 

Anonymous
Not applicable

Thanks this works great!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.