Forum Discussion

Bahama85's avatar
Bahama85
Copper Contributor
Sep 15, 2022

Power Query - Combine and Match

Alrighty, have a power query question for you all...

I have both of these tables set up to do their thing in PQ but I want to see if it's possible to do one last step. I know it's possible with formulas outside of PQ so I imagine it's possible to do it within. As you can tell in both tables, Date, Name, and Account headers all contain the same content within. I need PQ to match the hours to the criteria in the left table as shown per example (cell 4.78 to cell E4) as the criteria matches the (9/7/2022, Avila, Efrain, and 1st Floor Mezz. After that, it will help me start to set up some calculations for "lines per hour" and "pick percentage". Thank you in advance

 

 https://1drv.ms/x/s!Agmju254hK9cgjy9wMavMLdSf_ER 

excel
Formulas and Functions
  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 15, 2022

    Bahama85 You should see a third query in your workbook along with a third tab showing the results:  It requires only two steps

    1. Within the Table3 query, select Merge Queries from the menu,
    2. select Table2_2 in the merge window
    3. Hold CTRL and click on each of the three columns for Conf.dt., User, and PAr on both the upper and lower section of that window and click okay.  Ensure that whatever sequence you use to select the columns in the top is the same for the columns in the lower

    Now your table should have an extra column.  Press this little button int he corner of the Table2_2 cell and deselect everything but hours.  

     

    That will give you a single table with the results from both merged together. 

     

    Just in case it didn't save to your drive properly here are the two steps I added (within the editor, select "Advanced Editor, place a comma at the end of your last step and past the two steps prior to the "in" clause below.  

     

        #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Conf.dt.", "User", "PAr"}, Table2_2, {"Date", "Name", "Account"}, "Table2_2", JoinKind.LeftOuter),
    #"Expanded Table2_2" = Table.ExpandTableColumn(#"Merged Queries", "Table2_2", {"Hours"}, {"Table2_2.Hours"})
in
    #"Expanded Table2_2"

     

    As a side note, to handle all the re-naming in your query, you can create a name mapping table in your workbook with the original names as reported, and the "Display" names you'd like to see.  I added that to the workbook and my copy of the query so you can see how many steps it can eliminate.  

     

    Hope this helps,

    Dexter

Resources