JSON files into Power BI

[Anonymous]

I have many JSON files and my problem is that I had to add a new line of information, I thought that it will be enough to add that parameter in the first JSON file then when the PBI won't find the column it will leave it as blank. The difference between the first and second JSON is that the first has the reportName parameter, but the second not, is there any way to adapt a thing like TRY_CAST?

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Example json files:

{
  "datasets": "abcd",
  "reports": "qwe",
  "dashboard": "12345"
}

{
  "datasets": "abcd",
  "reports": "qwe",
  "dashboard": "12345",
  "excel": "noname"
}

1 Create a query vis this code:

let
    Source = (Parameter1 as binary) => let
        Source = Json.Document(Parameter1),
        #"Converted to Table" = Table.FromRecords({Source})
    in
        #"Converted to Table"
in
    Source

2 Add invoke custom column:

3 Expand the Query1 column and result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

I don't think the Table.TransformColumnTypes function supports the MissingFields.Ignore term (which would make this easy to solve). You can use try ... otherwise though. In the formula bar, put try after the = before the Table.TransformColumnTypes expression and then put otherwise after it and then the same expression (with the part referencing the missing column removed).

Error handling - Power Query | Microsoft Docs

Pat

[Anonymous]

Hi @mahoneypat , 

Thanks for the reply, my problem is that I have a fear that as we advance with that maybe more and more parameters should be added to the json, there is no any dynamic workaround, but I really understand the limitations and I know that Power BI is not developed to process JSONs.