Display Name of Header in a new column for specific columns with values null

[pranay_singh]

Hi,

I am facing an issue and not a great list and records. Need some help here.

I have a table to multiple columns. I want to check columns Standby 1 to Standby 8 and display the names of columns as text separate by a semicolon or comma in new columns for which the values are not null.

For Example Below:

Name	Number	SKU Name	Standby 1	Standby 2	Standby 3	Standby 4	Standby 5	Standby 6	Standby 7	Standby 8
PS 1	555	PSK 1	null	null	null	null	null	5	5.1	null
PS 2	123	PSK 2	null	null	4.5	6	7	null	null	null
PS 3	543	PSK 3	null	null	null	null	null	null	null	null
PS 4	234	PSK 4	5.5	6	null	null	null	null	null	null
PS 5	341	PSK 5	4	null	null	null	null	null	null	null
Expected Result with Additional Column
Name	Number	SKU Name	Standby 1	Standby 2	Standby 3	Standby 4	Standby 5	Standby 6	Standby 7	Standby 8	Standby Columns (New Column)
PS 1	555	PSK 1	null	null	null	null	null	5	5.1	null	Standby 6, Standby 7
PS 2	123	PSK 2	null	null	4.5	6	7	null	null	null	Standby 3, Standby 4, Standby 5
PS 3	543	PSK 3	null	null	null	null	null	null	null	null	null
PS 4	234	PSK 4	5.5	6	null	null	null	null	null	null	Standby 1, Standby 2
PS 5	341	PSK 5	4	null	null	null	null	null	null	null	Standby 1

[slorin · Accepted solution]

Hi

another solution

Add column :

Text.Combine(
   Table.SelectRows(
      Record.ToTable(_),
      each [Value]<>null and Text.StartsWith([Name], "Standby"))[Name],
    ", ")

Stéphane

[dufoq3]

Hi @pranay_singh, check also this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghWMFTSUTI1NQWSAcHeYF5eaU4OAQqk3FQPrjhWB2yUEZBvaGQMNcoIQ5eJHkifGRCbYzcYag7IBFMTmDnGxDkJt3EmQL6RsQnUOBOw02EOIdEokDZjE0OoUSCeCRnOigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
    // You can probably delete this step
    RaplaceValues = Table.TransformColumns(Source, {}, each if List.Contains({"null", ""}, _) then null else _),
    Ad_StandByColumns = Table.AddColumn(RaplaceValues, "Standby Columns", each
        [ a = List.Select(Record.FieldNames(_), (x)=> Record.Field(_, x) <> null and Text.StartsWith(x, "Standby", Comparer.OrdinalIgnoreCase)),
          b = if List.IsEmpty(a) then null else Text.Combine(a, "#(lf)")
        ][b], type text )
in
    Ad_StandByColumns

[pranay_singh]

Hi Thank you for this, It does work but it makes the query very slow.

[dufoq3]

Hi again, try this one and let me know please (it is significantly faster):

You can probably delete this step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghWMFTSUTI1NQWSAcHeYB4aAkmZ6oElYnXAWoyAbEMjY6gWI7hKEz2QWjMgNkcYANUDUm1qAtNjjGkNqnITINvI2ASq3ATsBJjhOLSApI1NDKFaQDwTHFbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
    // You can probably delete this step
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    AddedIndex = Table.AddIndexColumn(ReplaceBlankToNull, "Index", 0, 1, Int64.Type),
    UnpivotedColumns = Table.Unpivot(AddedIndex, List.Select(Table.ColumnNames(AddedIndex), each Text.StartsWith(_, "Standby", Comparer.OrdinalIgnoreCase)), "Attribute", "Value"),
    GroupedRows = Table.Group(UnpivotedColumns, {"Index"}, {{"Standby Columns", each Text.Combine([Attribute], ", "), type text}}),
    MergedQueries = Table.NestedJoin(AddedIndex, {"Index"}, GroupedRows, {"Index"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"Standby Columns"}, {"Standby Columns"}),
    RemovedColumns = Table.RemoveColumns(ExpandedGroupedRows,{"Index"})
in
    RemovedColumns

[SamInogic]

Hi,

It appears that you want to display the names of columns with null values in the "StandBy Columns" column.

Below is the code for reference where I retrieve data from Microsoft Dataverse:

let

    Source = CommonDataService.Database(""),             // Your Datasource

    dbo_account = Source{[Schema="dbo",Item="account"]}[Data],

    SelectedColumns = Table.SelectColumns(dbo_account, {"name", "StandBy1", "StandBy2"}),

    StandByColumns = Table.AddColumn(SelectedColumns, "StandBy Columns", each 

        Text.Combine(

            List.Select(

                List.RemoveItems(Table.ColumnNames(SelectedColumns), {"name"}), //To remove Unwanted Columns

                (col) => Record.Field(_, col) = null

            ), 

            ", "

        )

    )

in

    StandByColumns

Note:  Edit & paste this code in Advanced Editor of Power Query.

Hope this helps.

[pranay_singh]

Hi,
Actually, I want to display the opposite. I want to just select the specific columns and display the name of the columns with NON null values.

[slorin · Accepted solution]

Hi

another solution

Add column :

Text.Combine(
   Table.SelectRows(
      Record.ToTable(_),
      each [Value]<>null and Text.StartsWith([Name], "Standby"))[Name],
    ", ")

Stéphane

[pranay_singh]

Apologies, but one more question. If using Text.StartsWith. Is it possible to add that it only extracts a part of the text for each column Name. 
For Example my Column names always are
_Standby_Nameofphase1 (Phase)
_Standby_Nameofphase2 (Phase)
_Standby_Nameofphase3 (Phase)

So that when I am combine and getting the names of the headers for all non null values. It gets the name and then extracts the text after 2nd "_" (underscore) and before (Phase)

So it will
Nameofphase1,Nameofphase2,Nameofphase3

[slorin · Accepted solution]

Hi @pranay_singh 

Text.Combine(
  List.Transform(
     Table.SelectRows(
        Record.ToTable(_),
        each [Value]<>null and List.Contains( Your_List, [Name]))[Name],
     each Text.BetweenDelimiters(_, "_", " (", 1, 0)),
  ", ")

Stéphane 

[pranay_singh]

Thank you so much! This works.

[pranay_singh]

Hi,
Thank you so much for this. Is there way to not use the name logic. 
Like not mentioning Text starts with and use it by selecting the set of columns in a record or list.

[slorin]

Yes, with List.Contains

=Text.Combine(
  Table.SelectRows(
    Record.ToTable(_),
    each [Value]<>null and List.Contains({"Standby 1", "Standby 7"}, [Name]))[Name],
  ", ")

Stéphane

[ManuelBolz]

Hello @pranay_singh ,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.


If you have any questions, please feel free to get in touch. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LCsAgDAWvUlxnk197iG4El+L9r9EkxJa2CCoJbyax91LbhgWKqtpb2xnd53ikEMGAUMhqJE6FblLA2d3u8QxIx2mV6fB/zRsXq4klcYkvzOELxWMWTMU7WawYFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
    Replaced = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Standby 1", "Standby 2", "Standby 3", "Standby 4", "Standby 5", "Standby 6", "Standby 7", "Standby 8"}),
    AddCustomColumn = Table.AddColumn(Replaced, "Null Columns", each 
        let
            currentRow = _,
            columns = {"Standby 1", "Standby 2", "Standby 3", "Standby 4", "Standby 5", "Standby 6", "Standby 7", "Standby 8"},
            nullColumns = List.Select(columns, each Record.Field(currentRow, _) <> null)
        in
            Text.Combine(nullColumns, ", ")
    )
in
    AddCustomColumn

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[AlienSx]

let
    Source = your_table,
    col_names = List.Buffer(List.LastN(Table.ColumnNames(Source), 8)),
    selection = (lst) => 
        [nulls = List.PositionOf(lst, null, Occurrence.All, (x, y) => x <> y),
        names = List.Transform(nulls, (x) => col_names{x}),
        value = Text.Combine(names, ", ")][value],
    add_col = Table.AddColumn(Source, "SB columns", (x) => selection(List.LastN(Record.FieldValues(x), 8)))
in
    add_col