Forum Discussion
Dynamic copy Header value to not null rows
Hi,
Starting to work with Power BI and I still didn't find a solution for this problem.
I have a table and a list with specific headers.
I want to for each item from the list, go to the column whose header has the same value, and then assign the header value to each not null row.
col list({"Col B", "Col D")
Source
| Col A | Col B | Col C | Col D |
| null | null | asg | tr |
| ts | null | asf | null |
| as | ar | hger | er |
| null | ds | null | null |
Result
| Col A | Col B | Col C | Col D |
| null | null | asg | Col D |
| ts | null | asf | null |
| as | Col B | hger | Col D |
| null | Col B | null | null |
Thank you
Hi Anonymous
You can refer to the following suggestion
1.You can put the following code to the Adcanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVGJxelAsqRIKVYnWqmkGFkiDcYBSSWCpBKLgERGeiqISoVogapOQdIJ0RMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t, #"Col D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Col B", type text}, {"Col C", type text}, {"Col D", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each _, type table [Attribute=text, Value=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Value", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}) in #"Removed Columns"Then create a measure
Measure = IF(SELECTEDVALUE('Table (3)'[Attribute]) IN VALUES('Table (2)'[Column1])&&SELECTEDVALUE('Table (3)'[Value])<>"null",SELECTEDVALUE('Table (3)'[Attribute]),SELECTEDVALUE('Table (3)'[Value]))Then put the measure and the column of table(3) to the matrix visual
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
You can refer to the following suggestion
1.You can put the following code to the Adcanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVGJxelAsqRIKVYnWqmkGFkiDcYBSSWCpBKLgERGeiqISoVogapOQdIJ0RMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t, #"Col D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Col B", type text}, {"Col C", type text}, {"Col D", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each _, type table [Attribute=text, Value=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Value", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}) in #"Removed Columns"Then create a measure
Measure = IF(SELECTEDVALUE('Table (3)'[Attribute]) IN VALUES('Table (2)'[Column1])&&SELECTEDVALUE('Table (3)'[Value])<>"null",SELECTEDVALUE('Table (3)'[Attribute]),SELECTEDVALUE('Table (3)'[Value]))Then put the measure and the column of table(3) to the matrix visual
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.