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 want something the results as shown in the 2nd table.
if columns are empty, it should be repeating values horizontally from the previous cell on the left side of it.
Solved! Go to Solution.
You could try to transpose, fill down, transpose to get your desired result. Here is an example query with similar data. Note it also requires a step potentially to replace blanks with null to enable fill down. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYhCK1UHwnZEFcMi7KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column2", "Column3", "Column4", "Column5"}),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You could try to transpose, fill down, transpose to get your desired result. Here is an example query with similar data. Note it also requires a step potentially to replace blanks with null to enable fill down. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYhCK1UHwnZEFcMi7KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column2", "Column3", "Column4", "Column5"}),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |