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
kp_pbi
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
MarcelBeug
Community Champion
Community Champion

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
Community Champion
Community Champion

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)

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.

 

Specializing in Power Query Formula Language (M)

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
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.