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

Top Solution Authors
Top Kudoed Authors