Forum Discussion
Parsing JSON into rows
Hi
I'm new to power BI, and new to working with JSON files, so I can really use some help.
My data set is a csv file that contains only two columns: first one is a unique email address, and the other is json document formatted as such:
{
"aaa": {
"edition": "Ent",
"first_date": "2010-01-01",
"last_date": "2019-08-05",
"usage_data": "2501"
},
"bbb": {
"edition": "Free",
"first_date": "2018-02-25",
"last_date": "2019-01-03",
"usage_data": "23301"
}
}aaa and bbb are unique IDs, and every email cam have an unlimited number of IDs.
I would like to form this table:
email id edition first_date last_date usage_data
----------- --- ------- ---------- ---------- ----------
[email protected] aaa Ent 2010-01-01 2019-08-05 2501
[email protected] bbb Free 2018-02-25 2019-01-03 23301
[email protected] ccc Free 2016-04-16 2019-07-03 4556
when using the Power BI json parser, I got the IDs turned into columns.
I tried to use 'Unpivot', but it looks like there are way too many columns for this to work - i'm working on a csv with more than 100K IDs.
I have no idea how to approach this, any help will be appreciated.
3 Replies
Hi Yaa_D ,
After you load the data, you need to open query editor. Here is my test data which is close to you.
Then create a custom column “new”. Click Custom Column icon to open a sub window. Input the following codes.
=Json.Document([Column2])
Click “OK”.
Then you can remove “column 2” and get a table like the following image.
Expand the “new” column and unpivot these expanded columns by clicking Unpivot Columns option.
Expand the “Value” column and remove extra fields in the “Attribute” column by using replace values function. At last, rename the column names. Don’t forget to click Close & Apply after you finish it.
At the same time, I also provide the overall code that you can edit in the Advanced Editor.
let Source = Csv.Document(File.Contents("YOUR CSV FILE PATH"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.Csv]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "new", each Json.Document([Column2])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"}), #"Expanded new" = Table.ExpandRecordColumn(#"Removed Columns", "new", {"aaa", "bbb", "ccc"}, {"new.aaa", "new.bbb", "new.ccc"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded new", {"Column1"}, "Attribute", "Value"), #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"edition", "first_date", "last_date", "usage_data"}, {"Value.edition", "Value.first_date", "Value.last_date", "Value.usage_data"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Value","new.","",Replacer.ReplaceText,{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "email"}, {"Attribute", "id"}, {"Value.edition", "edition"}, {"Value.first_date", "first_date"}, {"Value.last_date", "last_date"}, {"Value.usage_data", "usage_data"}}) in #"Renamed Columns"Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply v-eachen-msft
I tried to do that, but as i mentioned, i have more than 100K Ids.I'm unable to expand and unpivot that many (at least using the GUI). am I missing something?
Hi Yaa_D ,
The 1000 limit is only for the display, it is actually effective.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
