locked
Conditional lookup using power query RRS feed

  • Question

  • Hi All,

    I am importing 2 tables from a folder in master file link below:-

    1) Date: Column I to AS are imported from original data and G & H I have inserted via Power Query.

    2) Techno_Mapping : This is another sheet from where we need to take reference to insert new columns in Data sheet.

    I am confused how to insert the new conditional columns.

    New Column Headers are :

    2G 3G 4G 2G Techno 3G Techno 4G Techno

    Logic for the same :-

    1) 2G column : If column H is 2G then column A should be Column G , otherwise lookup the respective site code of Column G in Techno_Mapping

    2) 3G column : If column H is 3G then column B should be Column G , otherwise lookup the respective site code of Column G in Techno_Mapping

    3) 4G column : If column H is 4G then column C should be Column G , otherwise lookup the respective site code of Column G in Techno_Mapping.

    When All these 3 column filled then lookup the respective code Techno in Techno_Mapping and fill the column D/E/F respectively.

    Can anybody suggest me how to do this conditional lookup in this case.

    Below is the link of the file

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

    Regards,

    Rajender


    Rajender

    Monday, December 4, 2017 3:48 PM

Answers

All replies

  • Hi Rajender,

    The logic that only applies within the table (e.g. if column H is 2G then column A should be Column G) should be do-able via a conditional colum. For the lookup into Techno_Mapping, you'll probably want to create a function you can call, passing the value of column G. Then edit the conditional column M script to call the function.

    Ehren

    Wednesday, December 6, 2017 11:21 PM
  • Hi Ehren,

    Thanks for your feedback, Actually I am basic user of Power query. Could you please assist how to create function and call to get desired output.

    Thanks for your support.

    Regards,

    Rajender


    Rajender

    Thursday, December 7, 2017 8:02 AM
  • Hi Ehren,

    Looking for your valuable support.

    Please assist.

    Regards,

    Rajender


    Rajender

    Monday, December 11, 2017 1:08 PM
  • Hi Rajender. Would it be possible for you to create a simplified and self-contained example of the kind of lookup you're wanting to do? The sample xlsx is pretty complicated, and is also missing the source files. Perhaps if I could demonstrate how to do this against a single, simple example, you would be able to apply it to your more complex scenario.

    Ehren

    Monday, December 11, 2017 7:22 PM
  • Hi Ehren,

    Sorry not to be clear in my previous post.

    I have created the raw dump file link in the sample file itself.

    Please suggest now, how to insert below mentioned columns as my 1st query in final file.

    2G 3G 4G 2G Techno 3G Techno 4G Techno

    Partially I have done in data sheet.

    Regards,

    Rajender


    Rajender

    Tuesday, December 12, 2017 9:41 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Reduced file from 5 MB to 25 KB by keeping only data
    that is necessary and sufficient to illustrate technique.
    http://www.mediafire.com/file/wcyzxreswzwbeov/12_08_17a.xlsx

    Ehren: Improvements welcome.

    • Marked as answer by AskQuery1984 Thursday, December 14, 2017 6:24 PM
    Thursday, December 14, 2017 1:02 AM
  • Hi Herbert,

    Thanks a lot for your prompt feedback and support. I have one query as per example in table 2 all sites are unique and we are indexing the same in power query.

    But in my original case sites codes are repeated , so want to know if still this approach will work ?

    Regards,


    Rajender

    Thursday, December 14, 2017 12:26 PM
  • I suggest you copy and paste some ( <10 rows) of your data into my template
    and check if it produces the results you want.
    If, after a lot of query tweaking, you cannot achieve your goal,
    share my edited template file (not your edited 5MB file) and your desired results here.

    Thursday, December 14, 2017 1:43 PM
  • Hi Herbert,

    Thanks a lot for your valuable support. I am able to implement the same in original file and its working fine.

    Now trying to understand the logic how it worked.

    Thanks once again.


    Rajender

    Thursday, December 14, 2017 6:23 PM