Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Team,
I have a dataset like this
The Column named "Data" is a Json column.
I am trying convert each record into a separate column with each value from JSON.
I tried to Parse it by right click->transform->JSON
but i got the output like this
Here i want convert each rows containing all the keys into separate columns
and the rows containing partial keys or blank should return blank
is it possible?
Solved! Go to Solution.
@Anonymous - unfortunately with JSON the data may not be in an ideal nested format that represents a table when extracted. You will have to expand each list to discern what is in it. You can pull those into separate columns. For example, you might have a column that looks like this:
Those are 5 integers. From there, I can convert those to text with List.Transform, then use Text.Combine. So the full M code for a new column to do this is:
#"Added Custom" =
Table.AddColumn(
Source,
"Combined",
each try
Text.Combine(
List.Transform(
[Column1],
Text.From
),
", "
)
otherwise [Column1]
)
That will return this:
So you may have to use a combination of if/then/else or try/otherwise (the latter is like IFERROR in Excel) with a number of Text and List functions to tease out the data as you need, but we'd have to know what each list contains, and with JSON, each list could be unique.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse the Value.Is function.
Create your Table.SelectRows statement using the UI - select anything, doesn't matter.
Then change the code from the 1st line (something like this) to the 2nd line.
each [Column1] = 1
each Value.Is([Column1], List.Type)
It will return this:
If you want things that are not a list, change the code to:
each Value.Is([Column1], List.Type) = false
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks Edhans for your help, it worked perfectly
@Anonymous - unfortunately with JSON the data may not be in an ideal nested format that represents a table when extracted. You will have to expand each list to discern what is in it. You can pull those into separate columns. For example, you might have a column that looks like this:
Those are 5 integers. From there, I can convert those to text with List.Transform, then use Text.Combine. So the full M code for a new column to do this is:
#"Added Custom" =
Table.AddColumn(
Source,
"Combined",
each try
Text.Combine(
List.Transform(
[Column1],
Text.From
),
", "
)
otherwise [Column1]
)
That will return this:
So you may have to use a combination of if/then/else or try/otherwise (the latter is like IFERROR in Excel) with a number of Text and List functions to tease out the data as you need, but we'd have to know what each list contains, and with JSON, each list could be unique.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Edhans,
One more question,
In this table below how can i only filter out values except Lists ( if i am trying to filter by clicking the button on the right of value, i am not finding the Lists to filter out)
Is it Possible to only convert JSON records and rest all to blank values??
Thanks @amitchandak for the help,
I managed to filter only the JSON records and parsed
then i am getting a mix of values and list as shown below
Here can i only expand the rows containg list and rest keep as it is?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
64 | |
52 | |
49 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |