Search code examples
powerbipowerquerym

Promote headers inside column containing tables

Suppose we have column Content which contains tables. I would like to transform each table of that column in such a way that all table get headers from first row.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsovVzBU0lFyVIrVgfCMUHjGKDwTFJ4pCs8MwosFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"Content", each _, type table}}),
    #"Previous Step" = Table.SelectColumns(#"Grouped Rows",{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Previous Step", "New Content", each Table.PromoteHeaders([Content], [PromoteAllScalars=true]))
in
    #"Added Custom"

If you select Previous Step from this code, and click where red arrow shows (right to the Table writing, not on it), you may see this inside:

enter image description here

Now I can get my desired results in added custom column in final step: enter image description here

But I would like get what I want without adding new column but by transforming Content column in final step. Is that possible?

Note that I do not want to expand the tables.

Solution

  • Just use Table.TransformColumns instead of Table.AddColumn

    Table.TransformColumns(
  #"Previous Step", 
  {{"Content", each Table.PromoteHeaders(_, [PromoteAllScalars = true])}}
)

    An easy way to make a transform columns step is to do something like add a text operation on a column from the Transform tab, which will create a TransformColumns step you can edit to do whatever you want instead.