Outer apply in Power Query M

Question

I have a table in Excel

Id
-----
1
2
3

Also I have a function F which takes Id and returns a table. I need to make something like

SELECT * FROM ExcelTable as et OUTER APPLY F(et.Id)

Do you have any ideas about how to make it in Power Query M without loop?

Answer 1

Seems like natural way to do it in Power Query M is to use AddColumn and ExpandColumn. Here is equivalent of

SELECT * 
FROM ExcelTable e 
OUTER APPLY (
            SELECT * 
            FROM SomeOtherTbl s 
            WHERE s.SomeOtherId = e.Id
            )

In Power query M it is

let
  SomeOtherTbl = Table.FromRecords({[SomeOtherId = 0, Field1 = "Hate MS docs"],
                                    [SomeOtherId = 1, Field1 = "samples formatting"]}),
  ExcelTable = Table.FromRecords({[Id = 0],
                                  [Id = 1]}),


  F = (id) => Table.SelectRows(SomeOtherTbl, each [SomeOtherId] = id),
  OuterApplyStep1 = Table.AddColumn(ExcelTable, "FFFF", each F([Id])),
  ColumnNames = Table.Schema(OuterApplyStep1{0}[FFFF])[Name],
  OuterApplyStep2 = Table.ExpandTableColumn(OuterApplyStep1, "FFFF", ColumnNames, ColumnNames)
in
  OuterApplyStep2

Nice thing is that I don't care about column names of the second table. Bad thing is that code fails if no match - Table.Schema falls.