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
EvPeCO
Frequent Visitor

Filtering out columns in M

I'm looking for a solution to filter out columns before it gets into Power Query Editor based on a value in the json result (it's being returned from an API Endpoint but created a simple json result). I've copied the M code below. I couldn't see a way to upload the PBIX file.

 

What I'm trying to do, is to filter out the columns where "schemaType":"Dynamic" and keep the columns where "schemaType":"Static". The static columns never change whereas the dynamic columns change (the name and field names change between result sets which is causing metadata issues and needs manual updates. The data we are reporting off of is only in the static columns anyways. 

 

Is there a way to filter out the dynamic columns?

 

@ImkeF helped me with the key and value parsing. Maybe you have a pointer for me on this?

 

 

 

 

let
    Source = "[#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""a"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            }#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t2""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    },#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""b"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK NAME"",#(cr)#(lf)                ""FieldName"":""TASK_Name"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":2,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Dynamic""#(cr)#(lf) }           #(cr)#(lf)#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t1"",#(cr)#(lf)                ""name1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t2"",#(cr)#(lf)                ""name2""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t3"",#(cr)#(lf)                ""name3""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    }#(cr)#(lf)]",
    #"Parsed JSON" = Json.Document(Source),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) ),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column1"})
in
    #"Removed Columns"

 

 

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @EvPeCO ,

 

You could expand the "Column1" with recode. Then you will get an extra column "schemaType".
4-2.PNG4-1.PNG

Now you need to remove rows whose schemaType is "Dynamic" with Table.SelectRows(). At last, remove duplicates.

let
    Source = "[#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""a"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            }#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t2""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    },#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""b"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK NAME"",#(cr)#(lf)                ""FieldName"":""TASK_Name"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":2,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Dynamic""#(cr)#(lf) }           #(cr)#(lf)#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t1"",#(cr)#(lf)                ""name1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t2"",#(cr)#(lf)                ""name2""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t3"",#(cr)#(lf)                ""name3""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    }#(cr)#(lf)]",
    #"Parsed JSON" = Json.Document(File.Contents("C:\Users\eadsc\Desktop\New Text Document.json")),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) ),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Custom", "Column1", {"columnMetadata"}, {"Column1.columnMetadata"}),
    #"Expanded Column1.columnMetadata" = Table.ExpandListColumn(#"Expanded Column1", "Column1.columnMetadata"),
    #"Expanded Column1.columnMetadata1" = Table.ExpandRecordColumn(#"Expanded Column1.columnMetadata", "Column1.columnMetadata", {"schemaType"}, {"Column1.columnMetadata.schemaType"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.columnMetadata1", each [Column1.columnMetadata.schemaType] <> "Dynamic"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.columnMetadata.schemaType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"TASK_Name"})
in
    #"Removed Duplicates"

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
EvPeCO
Frequent Visitor

Thanks for the replies and info @mahoneypat and @v-eachen-msft . I'll take a look at both suggestions later today and respond back.

v-eachen-msft
Community Support
Community Support

Hi @EvPeCO ,

 

You could expand the "Column1" with recode. Then you will get an extra column "schemaType".
4-2.PNG4-1.PNG

Now you need to remove rows whose schemaType is "Dynamic" with Table.SelectRows(). At last, remove duplicates.

let
    Source = "[#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""a"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            }#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t2""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    },#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""b"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK NAME"",#(cr)#(lf)                ""FieldName"":""TASK_Name"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":2,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Dynamic""#(cr)#(lf) }           #(cr)#(lf)#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t1"",#(cr)#(lf)                ""name1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t2"",#(cr)#(lf)                ""name2""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t3"",#(cr)#(lf)                ""name3""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    }#(cr)#(lf)]",
    #"Parsed JSON" = Json.Document(File.Contents("C:\Users\eadsc\Desktop\New Text Document.json")),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) ),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Custom", "Column1", {"columnMetadata"}, {"Column1.columnMetadata"}),
    #"Expanded Column1.columnMetadata" = Table.ExpandListColumn(#"Expanded Column1", "Column1.columnMetadata"),
    #"Expanded Column1.columnMetadata1" = Table.ExpandRecordColumn(#"Expanded Column1.columnMetadata", "Column1.columnMetadata", {"schemaType"}, {"Column1.columnMetadata.schemaType"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.columnMetadata1", each [Column1.columnMetadata.schemaType] <> "Dynamic"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.columnMetadata.schemaType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"TASK_Name"})
in
    #"Removed Duplicates"

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Thanks @v-eachen-msft We got this solution working. Not sure why we didn't go this path from the get go.  I appreciate you taking the time to provide your expertise and helping us solve this issue.

mahoneypat
Microsoft Employee
Microsoft Employee

Your post said you want to filter it out before it gets into the query editor.  The best way to do that is to add a $filter parameter to your API call (or whatever the syntax is for that API).  Are you able to do that?

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.