cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01
Super User
Super User

How to dynamically expand columns of a table with condition

@ImkeF @AlexisOlson @watkinnc @CNENFRNL 

 

I have a table like following

 

 

Source = #table({"row", "__name", "id","location","salary","age","stack"}, {        
        {1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
        ,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
    })

 

 

smpa01_0-1669848354811.png

 

How can I ask PQ to dynamically and efficiently expand all the columns that have a table in them?

I want to end up with this

 

smpa01_1-1669848478823.png

The full query is below

 

 

let
    Source = #table({"row", "__name", "id","location","salary","age","stack"}, {
        
        {1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
        ,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
    }),
    #"Expanded __name" = Table.ExpandTableColumn(Source, "__name", {"fName", "lName"}, {"fName", "lName"}), 
    #"Expanded location" = Table.ExpandTableColumn(#"Expanded __name", "location", {"country", "state"}, {"country", "state"}),
    #"Expanded stack" = Table.ExpandTableColumn(#"Expanded location", "stack", {"frontEnd", "backEnd"}, {"frontEnd", "backEnd"})
in
    #"Expanded stack"

 

 

Thank you in advance





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))

View solution in original post

AlexisOlson
Super User
Super User

@wdx223_Daniel's solutions is amazing but I also wanted to give this a shot without using any looping or recursion. I don't really know how efficient my solution is compared to other possibilities. Maybe @ImkeF has some intuition.

 

If your tables always have a single row, then you can take the first row as a record and then expand each row of your table like this:

 

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, #table({"fName", "lName"}, {{"John", "Doe"}}), 101, #table({"country", "state"}, {{"CA", "ON"}}), 100000, 50, #table({"frontEnd", "backEnd"}, {{null,   "SQL"}})}, 
      {2, #table({"fName", "lName"}, {{"Jane", "Doe"}}), 102, #table({"country", "state"}, {{"CA", "AB"}}), 200000, 35, #table({"frontEnd", "backEnd"}, {{"HTML", "SQL"}})}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Table.ColumnNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is table then Record.ToList(Table.First(_)) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

 

 

Here's a slightly cleaner equivalent with the source already in record (instead of table) form:

 

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, [fName="John", lName="Doe"], 101, [country="CA", state="ON"], 100000, 50, [frontEnd=null,   backEnd="SQL"]},
      {2, [fName="Jane", lName="Doe"], 102, [country="CA", state="AB"], 200000, 35, [frontEnd="HTML", backEnd="SQL"]}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

 

 

The logic for expanding the column names and column values is quite similar. Here's how AllCals is calculated:

 

AllCols
= List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    List.Transform(
      {"row", "__name", "id", "location", "salary", "age", "stack"},
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    {
      {"row"},
      Record.FieldNames(Record.Field(FirstRow, "__name")),
              /* = Record.FieldNames(FirstRow[__name])*/
      {"id"},
      Record.FieldNames(Record.Field(FirstRow, "location")),
              /* = Record.FieldNames(FirstRow[location])*/
      {"salary"},
      {"age"},
      Record.FieldNames(Record.Field(FirstRow, "stack"))
              /* = Record.FieldNames(FirstRow[stack])*/
    }
  )
= List.Union(
    {
      {"row"},
      {"fName", "lName"}
      {"id"},
      {"country", "state"},
      {"salary"},
      {"age"},
      {"frontEnd", "backEnd"}
    }
  )
= { "row", "fName", "lName", "id", "country", "state", "salary", "age", "frontEnd", "backEnd" }

 

 

The function fn_expandRowToList does nearly the same except that it reads the field values instead of the field names.

 

fn_expandRowToList(FirstRow)
= List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    )
= List.Union(
    List.Transform(
      Record.ToList(
        [
          row = 1,
          __name = [fName="John", lName="Doe"],
          id = 102,
          location = [country="CA", state="ON"],
          salary = 100000,
          age = 50,
          stack = [frontEnd=null, backEnd="SQL"]
        ]
      ),
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    List.Transform(
      {1, [fName="John", lName="Doe"], 102, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    {
      {1},
      Record.ToList([fName="John", lName="Doe"]),
      {101},
      Record.ToList([country="CA", state="ON"]),
      {100000},
      {50},
      Record.ToList([frontEnd=null, backEnd="SQL"]),
    }
  )
= {1, "John", "Doe", 101, "CA", "ON", 100000, 50, null, "SQL"}

 

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

Hi @AlexisOlson ,
nice solutions!
But I don't see any recursion in @wdx223_Daniel s solution. 
As he seems to call the source data less times, I would imagine his solution being faster.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I didn't mean to suggest Daniel's solution involves looping or recursion. It does not and I wanted to see if I could do something similar (as opposed to Chris Webb's solution I linked to, which is recursive).

AlexisOlson
Super User
Super User

@wdx223_Daniel's solutions is amazing but I also wanted to give this a shot without using any looping or recursion. I don't really know how efficient my solution is compared to other possibilities. Maybe @ImkeF has some intuition.

 

If your tables always have a single row, then you can take the first row as a record and then expand each row of your table like this:

 

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, #table({"fName", "lName"}, {{"John", "Doe"}}), 101, #table({"country", "state"}, {{"CA", "ON"}}), 100000, 50, #table({"frontEnd", "backEnd"}, {{null,   "SQL"}})}, 
      {2, #table({"fName", "lName"}, {{"Jane", "Doe"}}), 102, #table({"country", "state"}, {{"CA", "AB"}}), 200000, 35, #table({"frontEnd", "backEnd"}, {{"HTML", "SQL"}})}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Table.ColumnNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is table then Record.ToList(Table.First(_)) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

 

 

Here's a slightly cleaner equivalent with the source already in record (instead of table) form:

 

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, [fName="John", lName="Doe"], 101, [country="CA", state="ON"], 100000, 50, [frontEnd=null,   backEnd="SQL"]},
      {2, [fName="Jane", lName="Doe"], 102, [country="CA", state="AB"], 200000, 35, [frontEnd="HTML", backEnd="SQL"]}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

 

 

The logic for expanding the column names and column values is quite similar. Here's how AllCals is calculated:

 

AllCols
= List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    List.Transform(
      {"row", "__name", "id", "location", "salary", "age", "stack"},
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    {
      {"row"},
      Record.FieldNames(Record.Field(FirstRow, "__name")),
              /* = Record.FieldNames(FirstRow[__name])*/
      {"id"},
      Record.FieldNames(Record.Field(FirstRow, "location")),
              /* = Record.FieldNames(FirstRow[location])*/
      {"salary"},
      {"age"},
      Record.FieldNames(Record.Field(FirstRow, "stack"))
              /* = Record.FieldNames(FirstRow[stack])*/
    }
  )
= List.Union(
    {
      {"row"},
      {"fName", "lName"}
      {"id"},
      {"country", "state"},
      {"salary"},
      {"age"},
      {"frontEnd", "backEnd"}
    }
  )
= { "row", "fName", "lName", "id", "country", "state", "salary", "age", "frontEnd", "backEnd" }

 

 

The function fn_expandRowToList does nearly the same except that it reads the field values instead of the field names.

 

fn_expandRowToList(FirstRow)
= List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    )
= List.Union(
    List.Transform(
      Record.ToList(
        [
          row = 1,
          __name = [fName="John", lName="Doe"],
          id = 102,
          location = [country="CA", state="ON"],
          salary = 100000,
          age = 50,
          stack = [frontEnd=null, backEnd="SQL"]
        ]
      ),
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    List.Transform(
      {1, [fName="John", lName="Doe"], 102, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    {
      {1},
      Record.ToList([fName="John", lName="Doe"]),
      {101},
      Record.ToList([country="CA", state="ON"]),
      {100000},
      {50},
      Record.ToList([frontEnd=null, backEnd="SQL"]),
    }
  )
= {1, "John", "Doe", 101, "CA", "ON", 100000, 50, null, "SQL"}

 

wdx223_Daniel
Super User
Super User

= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors