none
Joining Tables All on Top Row RRS feed

  • Question

  • Hi,

    I'd like to merge multiple tables together, and have them all shifted to the top row, so they're not offset by nulls. Is this possible?

    Monday, July 15, 2019 1:21 PM

Answers

  • Thanks. Let's just say this isn't a very common request. :)

    If you have three tables (Table1, Table2, and Table3), you can do this via the following M:

    = Table.Transpose(Table.Combine(List.Transform({Table1, Table2, Table3}, each Table.Transpose(_))))

    Just modify the list of tables to suit your scenario.

    EDIT: And here's a more performant solution, courtesy of Curt. Just pass two tables (as long as the two tables have distinct column names) and it will horizontally append them.

    = (table1, table2) =>
    let
        Schema = List.Combine({Table.ColumnNames(table1), Table.ColumnNames(table2)}),
        Results = Table.FromRecords(List.Transform(List.Zip({Table.ToRecords(table1), Table.ToRecords(table2)}), (list) => Record.Combine(List.RemoveNulls(list))), Schema, MissingField.UseNull)
    in
        Results

    Ehren


    Thursday, July 18, 2019 10:12 PM
    Owner

All replies

  • Can you clarify what you want?

    Let's say you have two tables, A and B.

    A:
    Column1    Column2
    ABC           123

    B:
    Column1    Column2
    DEF            456

    What result are you wanting to see?

    Ehren


    Wednesday, July 17, 2019 11:54 PM
    Owner
  • Thanks for the response, I'll attempt to clarify a little.

    TableA:

    Column1   Column2

    ABC            123

    TableB:

    Column1   Column2

    DEF            456

    When joined, they would look like this:

    Column1   Column2   TableB.Column1   TableB.Column2

    ABC           123             null                        null

    null            null             DEF                        456

    When really, I don't care if they're lined up properly, I just want them all put together in one table with the data at the top, like this:

    Column1   Column2   TableB.Column1   TableB.Column2

    ABC           123             DEF                       456







    • Edited by patri0t82 Thursday, July 18, 2019 10:46 AM
    Thursday, July 18, 2019 10:44 AM
  • Hi there. One more question: do any of the tables have more than one row? If so, what should happen in those cases?

    Ehren

    Thursday, July 18, 2019 5:26 PM
    Owner
  • Well, the idea overall is that the tables have no relation to each other, it's just to create one combined table of many tables. If the longest column in Table B (for example) had sixty rows, and the longest column from TableA only had 10, the remaining empty rows would be populated by nulls.

    Sorry if this is confusing, I was really thinking it was a simple idea.

    Thursday, July 18, 2019 5:34 PM
  • Thanks. Let's just say this isn't a very common request. :)

    If you have three tables (Table1, Table2, and Table3), you can do this via the following M:

    = Table.Transpose(Table.Combine(List.Transform({Table1, Table2, Table3}, each Table.Transpose(_))))

    Just modify the list of tables to suit your scenario.

    EDIT: And here's a more performant solution, courtesy of Curt. Just pass two tables (as long as the two tables have distinct column names) and it will horizontally append them.

    = (table1, table2) =>
    let
        Schema = List.Combine({Table.ColumnNames(table1), Table.ColumnNames(table2)}),
        Results = Table.FromRecords(List.Transform(List.Zip({Table.ToRecords(table1), Table.ToRecords(table2)}), (list) => Record.Combine(List.RemoveNulls(list))), Schema, MissingField.UseNull)
    in
        Results

    Ehren


    Thursday, July 18, 2019 10:12 PM
    Owner
  • Sorry for the delay! Thanks you, your first code works perfectly. I'm not sure I need to try to figure out the second piece, but I do really appreciate it.

    In your first piece of code, I do lose my column names; I'm assuming this is unavoidable, but if there's a solution, that would be amazing.

    Thanks again, so much.

    Monday, July 22, 2019 11:13 AM