combining two rows into one row with new columns in powerquery

Hi Dave,

I"m not sure who firrst published that pattern, it could be found in several place. The idea is

let say you have two table, Names and Visas

First generate connection query for Visas like this

let
    Source = Excel.CurrentWorkbook(){[Name="Visas"]}[Content],

    // Type for ID shall be the same in both queries
    ChangeType = Table.TransformColumnTypes(Source,{{"Visa", type text}, {"Date", type date}}),

    // Within grouping add index for each ID value
    GroupByID = Table.Group(ChangeType, {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 0, 1), type table}}),
    ExpandGrouped = Table.ExpandTableColumn(GroupByID, "Count", {"Visa", "Date", "Index"}, {"Visa", "Date", "Index"}),
    UnpivotOtherThanIdAndIndex = Table.UnpivotOtherColumns(ExpandGrouped, {"ID", "Index"}, "Attribute", "Value"),

    // Merged values will be column names in final output
    MergeIndexAndAttribute = Table.CombineColumns(Table.TransformColumnTypes(UnpivotOtherThanIdAndIndex,
        {{"Index", type text}}, "en-US"),{"Index", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    PivotOnMerged = Table.Pivot(MergeIndexAndAttribute, List.Distinct(MergeIndexAndAttribute[Merged]), "Merged", "Value")


in
    PivotOnMerged

Main point here - index separatelly rows within each group if ID:s.

Anther query loads Names, merged it with Visas query result and lands output back to Excel

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    MergeWithVisas = Table.NestedJoin(Source,{"ID"},Visas,{"ID"},"Visas",JoinKind.LeftOuter),

    // Since column names are variable we pick them up from Visas query
    Columns = List.RemoveFirstN(Table.ColumnNames(Visas),1),

    // and use here
    ExpandMerged = Table.ExpandTableColumn(MergeWithVisas, "Visas", Columns, Columns)

in
    ExpandMerged

Here is not to hardcode column names.

Result is like this

and attached