Get Specfic column names from collection

[hemrhk]

I have a collection like following,

Column1	Column2	Column3
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

[Drrickryp]

@hemrhk 

Per Matt Deveney:

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

[rubin_boer · Accepted solution]

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

The column names in a column (Matts code)

Add the number of nulls/empty strings

apply final filter to visualise only column with empty string

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]

Thanks @Drrickryp . 

I have already tried this one.

[hemrhk]

Thanks @rubin_boer 

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