Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

JSON files into Power BI

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?

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.