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.
Hi all, I recieve a CSV in with different headings and sub headings.
the are a varrying number of headings and a varrying number of subheadings for each heading
column1 column2
heading1
subheading1 data
subheading2 data
subheading3 data
heading2
subheading4 data
subheading5 data
subheading6 data
subheading7 data
I need to extract the headings and append them as the first column of each row
column1 column2 column3
heading1 subheading1 data
heading1 subheading2 data
heading1 subheading3 data
heading2 subheading4 data
heading2 subheading5 data
heading2 subheading6 data
heading2 subheading7 data
In the edit query stage i have tried to do this by:
#"Add Heading Column" = Table.AddColumn(Source, "Heading", each if [Column2] = "" then [Column1] else ""),
column1 column2 column3
heading1 subheading1 data
subheading2 data
subheading3 data
heading2 subheading4 data
subheading5 data
subheading6 data
subheading7 data
I then tried to replace the blank values with the row aboves value. Im struggling to find out how to reference this.
#"Replaced Value" = Table.ReplaceValue(#"Add Heading Column","",{Previous Rows Value],Replacer.ReplaceValue,{"Heading"})
Is there a way to refernce the previous rows value like this?
Is there possibly a better way to do this ?
Any help would be much appreciated.
Solved! Go to Solution.
Not exact how to refer to the previous row but at least it works for the expected result.
Add a custom column
= Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null)
Then select the custom column and Fill down from transform ribbon
Remove the blanks in the data column 2
You end up with the table you needed
Full M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRUorViVYKLk0CsVMSSxJhfCNkPkyDEZIGYzQNJnB+LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Comumn 1" = _t, #"Column 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comumn 1", type text}, {"Column 2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column 2] = "data")) in #"Filtered Rows"
Not exact how to refer to the previous row but at least it works for the expected result.
Add a custom column
= Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null)
Then select the custom column and Fill down from transform ribbon
Remove the blanks in the data column 2
You end up with the table you needed
Full M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRUorViVYKLk0CsVMSSxJhfCNkPkyDEZIGYzQNJnB+LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Comumn 1" = _t, #"Column 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comumn 1", type text}, {"Column 2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column 2] = "data")) in #"Filtered Rows"
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |