Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to build an automated expand all columns for JSON data. I do not want to specify each column name, as this changes depending on the end-point used when getting the JSON data and I do not want to keep on maintaining the query every time something changes...
When I expand the JSON data manually, I have to click on the "Load more" button for all the columns to be loaded and shown...
When I use the Table.FromRecords command, I get the same result I would get if I DO NOT press the "Load more" button when I convert it manually. This would imply that the FromRecords command limits itself, but I do not have the option to "Load more" in the query.
I think this is a bug in the code as it does not create a complete table when using this command.
Do you have a work-around for this or can you create a bug for this issue?
In my example, doing it manually in the Desktop app, I get more than 200 columns, but using FromRecords I get 22 columns from the same data...
Here is the function I created to expand a column automatically:
ExpandAll = (recTable as table, columnToExpand as text) =>
let
result = Table.ExpandRecordColumn(
recTable,
columnToExpand,
Table.ColumnNames(
Table.FromRecords(
List.Select(
Table.Column(
recTable,
columnToExpand
),
each _ <> "" and _ <> null
)
)
)
)
in
result;
Any help would be appreciated, Thank you.
Francois Du Toit
Solved! Go to Solution.
Hi Everyone,
I want to thank @Jimmy801 & @CNENFRNL for your replies...
Just some feedback on the "solution" I figured out. I still believe the Table.FromRecords does not consider ALL the records to determine the columns for the table...
This function seems to work if your records do not contain the same fields for each record:
ExpandAll = (recTable as table, columnToExpand as text) =>
let
result = Table.ExpandRecordColumn(
recTable,
columnToExpand,
List.Distinct(
Record.FieldNames(
Record.Combine(
Table.Column(recTable, columnToExpand)
)
)
)
)
in
result;
The Combine is used to gather all the fields to get all the field names...
I hope this helps someone else 🙂
Kind regards,
Francois
In the latest version (0.2.3) of Power Query Connector SDK for Visual Studio Code this issue is still present.
We created another of workaround using records.
Utils.PaddedTable.FromRecords = (records as list) =>
let
fieldNames = List.Distinct(Record.FieldNames(Record.Combine(records))),
paddedRecords = List.Transform(records, each Record.SelectFields(_, fieldNames, MissingField.UseNull)),
asTable = Table.FromRecords(paddedRecords)
in
asTable,
I hope this also helps some people. It would also be good if MS would fix this or note it in the documentation.
Hi Everyone,
I want to thank @Jimmy801 & @CNENFRNL for your replies...
Just some feedback on the "solution" I figured out. I still believe the Table.FromRecords does not consider ALL the records to determine the columns for the table...
This function seems to work if your records do not contain the same fields for each record:
ExpandAll = (recTable as table, columnToExpand as text) =>
let
result = Table.ExpandRecordColumn(
recTable,
columnToExpand,
List.Distinct(
Record.FieldNames(
Record.Combine(
Table.Column(recTable, columnToExpand)
)
)
)
)
in
result;
The Combine is used to gather all the fields to get all the field names...
I hope this helps someone else 🙂
Kind regards,
Francois
Hi, @cfdutoit , it's totally feasible to debug by yourself by this means,
Create a blank query then using these code in the advanced editor,
let
recTable = ...,
columnToExpand = "columnName",
result = Table.ExpandRecordColumn(
recTable,
columnToExpand,
Table.ColumnNames(
Table.FromRecords(
List.Select(
Table.Column(
recTable,
columnToExpand
),
each _ <> "" and _ <> null
)
)
)
)
in
result
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello @cfdutoit
your function is written to expand only one Column a time and I can't see any json data. If you really want to expand all columns of a json data, look at this link to find a function that does exactly that
Here the link
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |