+ Reply to Thread
Results 1 to 8 of 8

Power Query merging

  1. #1
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Power Query merging

    Hello fellow Excel enthusiasts

    I have a huge and very complex setup in an excel file, that now uses a combination of VBA and formulas. I would like to try to convert a lot of that into Power Query instead, but am pretty unfamiliar with Power Query.

    I have tried to set up a very simplified demo sheet and would very much like a bit of help, getting started on how to add a couple of columns with lookup from other tables with Power Query.

    Hopefully the demo sheet is quite self explanatory from the picture below, otherwise I will gladly give a more detailed run down on the specific columns and their expected results if needed:
    PowerQuery.png

    Thanks in advance

    Best regards
    Imbizile
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query merging

    1. Remove duplicate from Units

    2.

    Please Login or Register  to view this content.
    3.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Power Query merging

    Amazing!

    Thanks so much Bo_Ry.

    I will look into what you did exactly in the M code and try to see if I can understand it.

    Again thanks a lot, really appreciate it.

  4. #4
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Power Query merging

    Hello again

    Was maybe a bit to fast to close this as solved, cause just realized that the OwnerID is not handled correctly.

    It does not seem to take into account that it should first lookup the owner with Relation = 8, and if that is not available then look up the owner with Relation = 17. If no 8 or 17, it should be blank.

    In the returned file, the owner for Unit1 is listed as XX22XX, which is incorrect:
    PowerQuery2.png

    Everything else seems to work like a charm though

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query merging

    Sorry, Please try again

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Power Query merging

    Thanks a lot for the effort Bo_Ry, but it is still not working 100% as intended in all scenarios. It now shows correct if relation is 8, but if there is no relation 8 and no relation 17 it should always be blank, which is not working.

    The logic is like this:
    If Relation = 8 Then
    Show that username
    Elseif Relation = 17 Then
    Show that username
    Else
    Dont show anything


    If I for instance change 934's relation to 10 instead of 17, then there is no relation 8 or 17 on Unit2 and then it should be blank. But it still returns a username:
    PowerQuery3.png

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query merging

    This should work

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Power Query merging

    And it does

    Thanks a bunch for your help, Bo_Ry

    Really appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  4. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  7. Power Query Solution for merging data
    By kersplash in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2018, 12:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1