Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors