Bug in combine binaries for JSON-files

[ImkeF]

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:

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

Thanks

[ImkeF]

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.

[v-qiuyu-msft]

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 

[ImkeF]

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!

[v-qiuyu-msft]

Hi @ImkeF, 

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

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

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

Best Regards,
Qiuyun Yu