Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm using PBI Desktop, and am parsing a JSON field into columns. I'm using the "Transform -> Parse ->JSON", function in Power BI. When i view the "Advanced Editor", the query looks like this:
let Source = Sql.Databases("dbname.database.windows.net"), DBASE = Source{[Name="dbname"]}[Data], dbo_scoutpm_json_esp = dbname{[Schema="dbo",Item="scoutpm_json_esp"]}[Data], #"Parsed JSON" = Table.TransformColumns(dbo_scoutpm_json_esp,{{"rows", Json.Document}}), #"Expanded rows" = Table.ExpandListColumn(#"Parsed JSON", "rows"), #"Expanded rows1" = Table.ExpandRecordColumn(#"Expanded rows", "rows", {"inspection", "latitude", "longitude"}, {"inspection", "latitude", "longitude"}), #"Expanded inspection" = Table.ExpandListColumn(#"Expanded rows1", "inspection"), #"Expanded inspection1" = Table.ExpandRecordColumn(#"Expanded inspection", "inspection", {"sample", "count","Index"}, {"sample", "count", "Index"}) in #"Expanded inspection1"
After "Expanded rows" , the column "rows" contains "Record" (as it shows on the PBI table), each with 3 entries.
Example - the table now looks like this
name 1, job 1, Record
name 2, job 2, Record
name 3, job 3, Record
(Record in this case has 3 values each)
In the step "Expanded rows1", these 3 are expanded into additional rows. This is exactly what i want, but, I want this step to add another column, which is an index, for each of the 3 records.
Example - This is what i want the data to look like afterwards
name 1, job 1, loc A, 1
name 1, job 1, loc B, 2
name 1, job 1, loc C, 3
name 2, job 2, loc A, 1
name 2, job 2, loc D, 2
name 2, job 2, loc G, 3
name 3, job 3, loc D, 1
name 3, job 3, loc H, 2
name 3, job 3, loc Z, 3
(In my example, Record in row 1 contained loc A,B,C row 2 contained loc A,D,G, row 3 contained loc D, H, Z)
Any help will be appreciated !
Solved! Go to Solution.
Your explanation raises some question marks with me, especially the statement that #"Expanded Rows1" would expand nested records into new rows: if a column with nested records is expanded, the number of rows will not change.
Anyhow, if I interpret your data structure correctly, then the "Added Index" step in the query below is what you are looking for (you may need to adjust the field names to your actual field names).
let #"Parsed JSON" = #table(type table[name = number, job = number, rows = list], {{1,1,{[location = {[loc = "A"],[loc = "B"],[loc = "C"]}]}}, {2,2,{[location = {[loc = "A"],[loc = "D"],[loc = "G"]}]}}, {3,3,{[location = {[loc = "D"],[loc = "H"],[loc = "Z"]}]}}}), #"Expanded rows" = Table.ExpandListColumn(#"Parsed JSON", "rows"), #"Expanded rows1" = Table.ExpandRecordColumn(#"Expanded rows", "rows", {"location"}, {"location"}), /* Column "location" has now nested lists, each containing a number of records (3). The step below adds the field Index to each of those records: In the inner List.Transform, the Index numbers are created {1..List.Count(_)} and converted to records. After List.Zip this looks like: {{[loc = "A"], [Index = 1]},{[loc = "B"], [Index = 2]},{[loc = "C"], [Index = 3]}} so a list in which each element has 2 separate records, each with 1 field. This list is transformed into a list of single records _{0}&_{1}, each with 2 fields: {[loc = "A", Index = 1],[loc = "B", Index = 2],[loc = "C", Index = 3]} */ #"Added Index" = Table.TransformColumns( #"Expanded rows1", {{"location", each List.Transform( List.Zip({_, List.Transform( {1..List.Count(_)}, each [Index = _])}), each _{0}&_{1}), type list}}), #"Expanded location" = Table.ExpandListColumn(#"Added Index", "location"), #"Expanded location1" = Table.ExpandRecordColumn(#"Expanded location", "location", {"loc", "Index"}, {"loc", "Index"}) in #"Expanded location1"
Your explanation raises some question marks with me, especially the statement that #"Expanded Rows1" would expand nested records into new rows: if a column with nested records is expanded, the number of rows will not change.
Anyhow, if I interpret your data structure correctly, then the "Added Index" step in the query below is what you are looking for (you may need to adjust the field names to your actual field names).
let #"Parsed JSON" = #table(type table[name = number, job = number, rows = list], {{1,1,{[location = {[loc = "A"],[loc = "B"],[loc = "C"]}]}}, {2,2,{[location = {[loc = "A"],[loc = "D"],[loc = "G"]}]}}, {3,3,{[location = {[loc = "D"],[loc = "H"],[loc = "Z"]}]}}}), #"Expanded rows" = Table.ExpandListColumn(#"Parsed JSON", "rows"), #"Expanded rows1" = Table.ExpandRecordColumn(#"Expanded rows", "rows", {"location"}, {"location"}), /* Column "location" has now nested lists, each containing a number of records (3). The step below adds the field Index to each of those records: In the inner List.Transform, the Index numbers are created {1..List.Count(_)} and converted to records. After List.Zip this looks like: {{[loc = "A"], [Index = 1]},{[loc = "B"], [Index = 2]},{[loc = "C"], [Index = 3]}} so a list in which each element has 2 separate records, each with 1 field. This list is transformed into a list of single records _{0}&_{1}, each with 2 fields: {[loc = "A", Index = 1],[loc = "B", Index = 2],[loc = "C", Index = 3]} */ #"Added Index" = Table.TransformColumns( #"Expanded rows1", {{"location", each List.Transform( List.Zip({_, List.Transform( {1..List.Count(_)}, each [Index = _])}), each _{0}&_{1}), type list}}), #"Expanded location" = Table.ExpandListColumn(#"Added Index", "location"), #"Expanded location1" = Table.ExpandRecordColumn(#"Expanded location", "location", {"loc", "Index"}, {"loc", "Index"}) in #"Expanded location1"
You sir... are indeed the master... !
Thanks a million - works like a bomb...
For me to understand - did this basically add a value to the list, which it then expanded into rows afterwards ?
It added field Index to the nested lists of records.
In step "Expanded location" the lists of records are expanded to rows with single records.
This was already the case in the original situation.
In step "Expanded location1" the records are expanded into 2 columns.
The data in the original JSON object always contains 25 "sets". Not all of them are populated though, and some have a value for "false" which is the value our application writes when there is not value in. I thus end up with rows which has "false" as a value, and I want to remove this from the data set. I can add a step at the end:
#"Filtered Rows" = Table.SelectRows(#"Expanded inspection1", each ([loc] <> false))
But I am wondering it will be better to delete these rows ?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |