cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hemrhk
Responsive Resident
Responsive Resident

Get Specfic column names from collection

I have a collection like following,

 

Column1Column2Column3
Value1""""

 

I want to retrieve the specific column names from the collection.

For Eg: I want to filter the column with empty values and it should return the column names in the collection 

or need to build a collection with column names that have empty rows.

 

For Eg:

Data

Column2

Column3

 

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @hemrhk as per @Drrickryp this code from Matt will give you the column names. you may want to add to the code the following:

 

Assumptions; here we have three columns called col1, col2, col3

Create a new collection colAll (or as you wish) and have it count the number of nulls or empty strings in it. the Switch statement will run the filter on the desired column (i am not aware if this can be done dynamically yet)

 

ClearCollect(
    colAll,
    AddColumns(
        colHeaders,
        "Have Nulls",
        CountRows(
            Filter(
                colGetNullColumns,
                Switch(
                    true,
                    Result = "col1",
                    col1 = "",
                    Result = "col2",
                    col2 = "",
                    Result = "col3",
                    col3 = ""
                )
            )
        )
    )
)

 

 

now that you have colAll to get all the columns which have empty strings or null is another filter

 

Filter(
    colAll,
    'Have Nulls' > 0
)

 

 

Result:

The original collection

rubin_boer_0-1655659060413.png

 

The column names in a column (Matts code)

rubin_boer_1-1655659088446.png

 

Add the number of nulls/empty strings

rubin_boer_2-1655659125542.png

 

apply final filter to visualise only column with empty string

rubin_boer_3-1655659153980.png

 

Hope it helps,

R

Ps colHeaders is the implementation to get the column names

Collect(
     colHeaders,
     Distinct(
         Ungroup(
             MatchAll(
                 JSON(
                     colGetNullColumns,
                     JSONFormat.IgnoreBinaryData                 
                ),
                 "([^""]+?)""\s*:"
             ).SubMatches,
             "SubMatches"
         ),
         Value
     )
 );

 

 

 

 

View solution in original post

4 REPLIES 4
Drrickryp
Super User
Super User

@hemrhk 

Per Matt Deveney:

 Set(
     myJSON,
     Distinct(
         Ungroup(
             MatchAll(
                 JSON(
                     mycollection,
                     JSONFormat.IgnoreBinaryData
                 ),
                 "([^""]+?)""\s*:"
             ).SubMatches,
             "SubMatches"
         ),
         Value
     )
 )

 

rubin_boer
Super User
Super User

hi @hemrhk as per @Drrickryp this code from Matt will give you the column names. you may want to add to the code the following:

 

Assumptions; here we have three columns called col1, col2, col3

Create a new collection colAll (or as you wish) and have it count the number of nulls or empty strings in it. the Switch statement will run the filter on the desired column (i am not aware if this can be done dynamically yet)

 

ClearCollect(
    colAll,
    AddColumns(
        colHeaders,
        "Have Nulls",
        CountRows(
            Filter(
                colGetNullColumns,
                Switch(
                    true,
                    Result = "col1",
                    col1 = "",
                    Result = "col2",
                    col2 = "",
                    Result = "col3",
                    col3 = ""
                )
            )
        )
    )
)

 

 

now that you have colAll to get all the columns which have empty strings or null is another filter

 

Filter(
    colAll,
    'Have Nulls' > 0
)

 

 

Result:

The original collection

rubin_boer_0-1655659060413.png

 

The column names in a column (Matts code)

rubin_boer_1-1655659088446.png

 

Add the number of nulls/empty strings

rubin_boer_2-1655659125542.png

 

apply final filter to visualise only column with empty string

rubin_boer_3-1655659153980.png

 

Hope it helps,

R

Ps colHeaders is the implementation to get the column names

Collect(
     colHeaders,
     Distinct(
         Ungroup(
             MatchAll(
                 JSON(
                     colGetNullColumns,
                     JSONFormat.IgnoreBinaryData                 
                ),
                 "([^""]+?)""\s*:"
             ).SubMatches,
             "SubMatches"
         ),
         Value
     )
 );

 

 

 

 

hemrhk
Responsive Resident
Responsive Resident

Thanks @Drrickryp . 

 

I have already tried this one.

hemrhk
Responsive Resident
Responsive Resident

Thanks @rubin_boer 

 

It is working as expected, This approach did not strike previously. Thanks again for helping out !!!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,656)