Parsing JSON into rows

[Yaa_D]

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.

[v-eachen-msft]

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.

[Yaa_D]

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?

[v-eachen-msft]

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.