Forum Discussion
Custom Column with column names that have null registers
Hello everybody. I need some help here with a customer request.
My database is one that has several columns, including columns with the names of documents that the supplier needs to deliver to complete the registration process.
The client asked me to put a column with the documents that the supplier must deliver, so, I need a column that contains the name of the columns where the record of that line is null. As in DAX it is not possible to bring the column name, it needs to be in Power Query, and I'm having a lot of difficulties to do that.
Below is an example of the base and the expected result in the column "Documents Missing".
Thank you for the help
| Supplier | Date | Status | Doc 1 | Doc 2 | Doc 3 | Doc 4 | Doc 5 | Documents Missing |
| Mr. John | jan/22 | Active | ok | ok | ok | ok | ok | None |
| Mr. Dave | fev/22 | Pendind | ok | ok | ok | Doc 2; Doc 5 | ||
| Ms. Claire | mar/22 | Active | ok | ok | ok | ok | ok | None |
| Ms. Marie | abr/22 | Pendind | ok | Doc 2; Doc 3; Doc 4; Doc 5 | ||||
| Mr. Kevin | jan/22 | Pendind | ok | Doc 1; Doc 2; Doc 4; Doc 5 | ||||
| Mr. Carl | fev/22 | Pendind | ok | ok | ok | ok | Doc 5 | |
| Ms. Eva | mar/22 | Active | ok | ok | ok | ok | ok | None |
Hi Anonymous
I have pasted the Advanced Editor code below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i3SU/DKz8hT0lHKSszTNzICMhyTSzLLUoGM/GysRKwORJ9LIlhVWmoZRF9Aal5KZl4KTKUCij4FiLZiPQXnnMTMIpDG3MQiEiwE6vRNLMoEKUtMKsJpIwLDnOmdWpaJ4j+ELgVkZ6Lqck4sysHjOTQC4TvXskSSvBYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Date = _t, Status = _t, #"Doc 1" = _t, #"Doc 2" = _t, #"Doc 3" = _t, #"Doc 4" = _t, #"Doc 5 " = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Supplier", "Date", "Status"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Document Missing", each if [Value] <> "ok" then [Attribute] else null),
#"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Supplier"}, {{"Grouped Table", each _, type table [Supplier=nullable text, Date=nullable text, Status=nullable text, Attribute=text, Value=text, Custom=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Documents Missing", each Table.Column([Grouped Table],"Document Missing")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Documents Missing", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Expanded Grouped Table" = Table.ExpandTableColumn(#"Extracted Values", "Grouped Table", {"Date", "Status", "Attribute", "Value"}, {"Date", "Status", "Attribute", "Value"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped Table",{"Supplier", "Date", "Status", "Documents Missing", "Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Attribute]), "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","","None",Replacer.ReplaceValue,{"Documents Missing"})
in
#"Replaced Value"This is what i got
If this post helps, Accept it as a solution
2 Replies
Hi Anonymous
I have pasted the Advanced Editor code below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i3SU/DKz8hT0lHKSszTNzICMhyTSzLLUoGM/GysRKwORJ9LIlhVWmoZRF9Aal5KZl4KTKUCij4FiLZiPQXnnMTMIpDG3MQiEiwE6vRNLMoEKUtMKsJpIwLDnOmdWpaJ4j+ELgVkZ6Lqck4sysHjOTQC4TvXskSSvBYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Date = _t, Status = _t, #"Doc 1" = _t, #"Doc 2" = _t, #"Doc 3" = _t, #"Doc 4" = _t, #"Doc 5 " = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Supplier", "Date", "Status"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Document Missing", each if [Value] <> "ok" then [Attribute] else null),
#"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Supplier"}, {{"Grouped Table", each _, type table [Supplier=nullable text, Date=nullable text, Status=nullable text, Attribute=text, Value=text, Custom=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Documents Missing", each Table.Column([Grouped Table],"Document Missing")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Documents Missing", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Expanded Grouped Table" = Table.ExpandTableColumn(#"Extracted Values", "Grouped Table", {"Date", "Status", "Attribute", "Value"}, {"Date", "Status", "Attribute", "Value"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped Table",{"Supplier", "Date", "Status", "Documents Missing", "Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Attribute]), "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","","None",Replacer.ReplaceValue,{"Documents Missing"})
in
#"Replaced Value"This is what i got
If this post helps, Accept it as a solution