Forum Discussion

Dave Belcher's avatar
Dave Belcher
Brass Contributor
Jun 16, 2018

combining two rows into one row with new columns in powerquery

UHi

Is it possible to make a powerquery that returns ONE row from the main table and MANY rows from the related table all combined in one row on the query?

This is because I want to use the row in a mailmerge, which can only be one record at a time.

 

For example, the main table is students, and the fields are name, ID number, age, nationality.

The related table (one to many) is visas and the table contains multiple rows for each student ID. The fields would be start date, end date, visa type.

 

So I want one row retuned that shows:

 

name | ID | age | nationality | visa 1 start date | visa 1 end date | visa 1 type | visa 2 start date | visa 2 end date... etc.

 

Note there could be any number of visa records related to that student, but normally 1, 2 or 3

BI & Data Analytics
Need Help

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 16, 2018

    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