Table.ExpandColumnTable inside Records

[JustDavid]

PQ and M Gurus,

I'm trying to know if 'Added Custom' columns each individual steps AND finally expand the columns can be done in 1 step.

For example, below is my screenshot where I try to filter before I expand on each step, which is my desired result.

Before Expansion

However, when I try to combine the last 3 steps into 1, it gave me an error. Specifically the last step where I wasn't able to expand.

Am wondering if it's doable. If it is, how?

Below are my code

=[
  getBinaryContent = Excel.Workbook([Content], true),
  getDesiredSheet = Table.SelectRows
              (getBinaryContent, each [Name] = "Sheet1"),
  ExpandToData = Table.ExpandTableColumn(getDesiredSheet, "getDesiredSheet", {"Data"}, {"Data"})
]

Thank you!

[Anonymous]

Hi @JustDavid ,

Please try:

#"Combined Step" = Table.ExpandTableColumn(
    Table.AddColumn(
        Table.AddColumn(#"FilterTo'OpenItemsReport.xlsx'", "getBinaryContent", each Excel.Workbook([Content], true)),
        "getDesiredSheets", each Table.SelectRows([getBinaryContent], each [Name]="Sheet1")
    ),
    "getDesiredSheets", {"Data"}, {"Data"}
)

 Agree with lbendlin that merging multiple steps should be avoided as much as possible, it makes the query less intuitive and harder to check for errors.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

There are Issue on your formula for expanding related to argument 2, 3 and 4.

in the second argument you sould mention the name of column including table which is column "Data" so replace "getDesiredSheet" with "Data" in the second arguemt.

THe third argument is alist including the column names that you will see after expanding, so replace the third argument with list of column names in the tables. and also remove the last argument.

[lbendlin]

Define what you mean by "one step"

=  Table.ExpandTableColumn(Table.SelectRows(Excel.Workbook([Content], true), each [Name] = "Sheet1"), "getDesiredSheet", {"Data"}, {"Data"})

is not better than what you did before, in fact it is harder to maintain.