ExpandRecordColumn when I do not know all the possible field names

[annaselway]

Hi,

Firstly, I have JSON files that I'm using the folder import to bring into PowerBI. However, the JSON file is putting the value I want that is currenly a field name as a column header, when really I want to create a new column that populates with the threshold value. 

So firstly, I want to expand out a record column but the field names for that record will be added to each time a new size of threshold is added, here I have a threshold value of 10000 and 500. I know how to write the M to expand out from record Value.products.Available.thresholds when I know the threshold value could be 10000 or 500, but how do i future proof this, so how can I write some M that will say just expand out this record column to as many columns as required based on the value that is returned. 

#"Expanded Value.productsAvailable.thresholds" = Table.ExpandRecordColumn(#"Expanded Value.isImportComplete.thresholds", "Value.productsAvailable.thresholds", {"value == 10000", "value == 500"}, {"Value.productsAvailable.thresholds.value == 10000", "Value.productsAvailable.thresholds.value == 500"}),

After this, I want to then create a new column where the value after == is what is populated into the column when the value is either true or false in the expanded column: 

 #"Added Conditional Column" = Table.AddColumn(#"Expanded Value.productsAvailable.thresholds", "Available Upload Threshold", each if [#"Value.productsAvailable.thresholds.value == 500"] = true then "500" else if [#"Value.productsAvailable.thresholds.value == 500"] = false then "500" else if [#"Value.productsAvailable.thresholds.value == 10000"] = true then "10000" else if [#"Value.productsAvailable.thresholds.value == 10000"] = false then "10000" else null)

Again, this code above 'works' it provides my desired column from the two previous columns, but it's not future proof each time i get a new value for this threshold i'll have to manually add it to the M. Has anyone got an idea of how else I could do this? What i could do to make this future proof? 

Here's some screen shots of the columns as well: 

And the JSON that this is from, it's the bold bit that I want to get out. 

"productsAvailable": {
            "max": 500,
            "min": 0,
            "thresholds": {
                "value == 500": false
            },
            "value": 500
        },

and 

 "productsAvailable": {
            "max": 1893,
            "min": 0,
            "thresholds": {
                "value == 10000": true
            },
            "value": 1893
        },

Thanks for any help you can provide 🙂 

[Jimmy801]

Hello @annaselway 

find here enclosed a future proof code 🙂

Basically the step GetRecordFields  does the trick. It extracts all your record-fieldnames and makes a distinct list. This list is then used to expand the column. So you can be 100% sure that your treshold-column is expanded fully.

let
    FirstJson = Json.Document("{
            ""max"": 500,
            ""min"": 0,
            ""thresholds"": {
                ""value == 500"": false
            },
            ""value"": 500
        }"),
    SecondJson = Json.Document("{
            ""max"": 1893,
            ""min"": 0,
            ""thresholds"": {
                ""value == 10000"": true
            },
            ""value"": 1893
        }"),
    TableFromJson = Table.FromRecords({FirstJson,SecondJson}),
    GetRecordFields = List.Distinct(List.Accumulate(TableFromJson[thresholds], {}, (s,c)=> s&Record.FieldNames(c))),
    ExpandThresholds = Table.ExpandRecordColumn(TableFromJson, "thresholds",GetRecordFields, GetRecordFields)
in
    ExpandThresholds

 Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[annaselway · Accepted solution]

Hi Jimmy, 

Thanks for your reply. I ended up actually finding a solution using the function approach detailed in this blog post: https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po.... I imported this as a second query, then used unpivot to change the table to have my fields in the rows. Then from here I could merge this back into the original table. 

Thanks, 

Anna