Custom Column with column names that have null registers

[Jonas_Jordao]

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

[Anonymous · Accepted solution]

Hi @Jonas_Jordao 

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

[Jonas_Jordao]

Yeah, it worked perfectly. Thanks a lot for the help.