Is this merge possible in PQ?
Results 1 to 2 of 2

Thread: Is this merge possible in PQ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is this merge possible in PQ?

    Hey folks,
    Suppose I create two queries for the two tables in my spreadsheet
    https://1drv.ms/x/s!AvjBsEPEq12ngTmY...jU4w7?e=9d2PBI

    Is it possible to somehow merge them as per my example?

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is this merge possible in PQ?

    if the names in two tables are different names you'll get empty table as result

    Daniel <> Daniel1

    or you can try with Index
    Code:
    
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Index = Table.AddIndexColumn(Source, "Index", 1, 1)
    in
        Index
    
    // Table2
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Sales", Int64.Type}, {"Date", type date}, {"Ref", type text}}),
        Index = Table.AddIndexColumn(Type, "Index", 1, 1)
    in
        Index
    
    // Merge1
    let
        Source = Table.NestedJoin(Table1,{"Index"},Table2,{"Index"},"Table2",JoinKind.LeftOuter),
        ExpandT2 = Table.ExpandTableColumn(Source, "Table2", {"Name", "Sales", "Date", "Ref"}, {"Name.1", "Sales", "Date", "Ref"}),
        ROC = Table.SelectColumns(ExpandT2,{"Name", "Sales", "Date", "Ref"})
    in
        ROC
    IMO it doesn't make sense
    Last edited by sandy666; Aug 13th, 2019 at 07:24 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •