cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kp_pbi Frequent Visitor
Frequent Visitor

Counter which adds +1 in Table.ExpandListColumn

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Counter which adds +1 in Table.ExpandListColumn

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"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug Super Contributor
Super Contributor

Re: Counter which adds +1 in Table.ExpandListColumn

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"
Specializing in Power Query Formula Language (M)

View solution in original post

kp_pbi Frequent Visitor
Frequent Visitor

Re: Counter which adds +1 in Table.ExpandListColumn

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 ?

MarcelBeug Super Contributor
Super Contributor

Re: Counter which adds +1 in Table.ExpandListColumn

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.

 

Specializing in Power Query Formula Language (M)
Highlighted
kp_pbi Frequent Visitor
Frequent Visitor

Re: Counter which adds +1 in Table.ExpandListColumn

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 ?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,626 guests
Please welcome our newest community members: