Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yaa_D
Frequent Visitor

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

-----------                  ---          -------     ----------           ----------           ----------

foo@foo.com            aaa         Ent          2010-01-01       2019-08-05        2501

foo@foo.com            bbb        Free        2018-02-25       2019-01-03        23301

other@1.com            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 3
v-eachen-msft
Community Support
Community Support

Hi @Yaa_D ,

 

After you load the data, you need to open query editor. Here is my test data which is close to you.4-1.PNG

Then create a custom column  “new”. Click Custom Column icon to open a sub window. Input the following codes.

=Json.Document([Column2])

Click “OK”.4-2.PNG

Then you can remove “column 2” and get a table like the following image.4-3.PNG

Expand the “new” column and unpivot these expanded columns by clicking Unpivot Columns option.4-4.PNG

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.4-5.PNG

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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?

 

expand.PNG

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.