cancel
Showing results for 
Search instead for 
Did you mean: 

Bug in combine binaries for JSON-files

There is a bug in the combine binaries for JSON-files:

The function that is used to transform the parsed JSON (Json.Document) is "Record.ToTable". So this will work if the "first-level"-element of the JSON is actually a record. But it could be a list as well and then the transformation fails.

 

Instead, please use the function "Table.FromRecords" instead like so:

 

if Value.Is(Source, type record) then Table.FromRecords( { Source } ) else Table.FromRecords( Source )

 

This has the other huge advantage, that the form of the output is suitable for further parsing into a dimensional model, as the key-columns can easily be kept:

 

image.png

 

instead, the current expierence is so, which requires further transformations to parse out the tables:

 

image.png

 

 

Thanks

 

Status: New
Comments
Super User

coming to think about it: You probably have to maintain the current behaviour on records for existing reports. Then this might be it:

 

if Value.Is(Source, type record) then Record.ToTable( Source ) else Table.FromRecords( Source )

But how about adding a step before where the alternative output format for records would be returned that people could choose for new reports?

 

Table.FromRecords( { Source } )

So that step would simply be ignored if not directly referenced.

Moderator

Hi @ImkeF,

 

Is it possible to share pbix file and source json files with us? You can upload them to your OneDrive and send the share link to me via private message if the report contains sensitive data. 

 

Best Regards,
Qiuyun Yu 

Super User

Hi @v-qiuyu-msft ,

please find the file incl. source files here: https://1drv.ms/f/s!Av_aAl3fXRbehcRkgBjAKNGN4FTCug

 

I've also included the solution that I would prefer (folder: "Desired Solution")

 

Thanks!

Moderator

Hi @ImkeF

 

The function Record.ToTable() function doesn't support a list of record, see: 

 

q4.PNG

 

For the query Transform Sample File from ImportMulitpleJSON (4) in your sample pbix file, we should use Table.FromList() instead of Record.ToTable() function, 

 

q5.PNG

 

You can look into updated pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EdUh1IhxrRBAnOOUgZ0...

 

Best Regards,
Qiuyun Yu