Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
pranay_singh
Frequent Visitor

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

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:

NameNumberSKU NameStandby 1Standby 2Standby 3Standby 4Standby 5Standby 6Standby 7Standby 8 
PS 1555PSK 1nullnullnullnullnull55.1null 
PS 2123PSK 2nullnull4.567nullnullnull 
PS 3543PSK 3nullnullnullnullnullnullnullnull 
PS 4234PSK 45.56nullnullnullnullnullnull 
PS 5341PSK 54nullnullnullnullnullnullnull 

 

 

 

 

Expected Result with Additional Column        
NameNumberSKU NameStandby 1Standby 2Standby 3Standby 4Standby 5Standby 6Standby 7Standby 8Standby Columns (New Column)
PS 1555PSK 1nullnullnullnullnull55.1nullStandby 6, Standby 7
PS 2123PSK 2nullnull4.567nullnullnullStandby 3, Standby 4, Standby 5
PS 3543PSK 3nullnullnullnullnullnullnullnullnull
PS 4234PSK 45.56nullnullnullnullnullnullStandby 1, Standby 2
PS 5341PSK 54nullnullnullnullnullnullnullStandby 1
2 ACCEPTED SOLUTIONS
slorin
Super User
Super User

Hi

another solution

 

Add column :

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

Stéphane

View solution in original post

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 

View solution in original post

13 REPLIES 13
dufoq3
Super User
Super User

Hi @pranay_singh, check also this:

 

Result

dufoq3_0-1718113221093.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

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

 

You can probably delete this step:

dufoq3_0-1718172983264.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

SamInogic
Super User
Super User

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.

 

 

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

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
Super User
Super User

Hi

another solution

 

Add column :

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

Stéphane

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

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 

Thank you so much! This works.

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.

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
Super User
Super User

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
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.