Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
Solved! Go to Solution.
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.
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.
Thanks this works great!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |