Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Convert DAX to M Query

Hi all.

I have a requirement wherein I want to convert DAX logic into M query.

Below is the DAX query which I am looking to convert.

TabIndexParent = IF(Tab[ParentTab]="00000000-0000-0000-0000-000000000000",
Tab[TabIndex],
LOOKUPVALUE(Tab[TabIndex],
Tab[Key],
Tab[ParentTab] & Tab[BID]))
 
 
Thanks,
Sanket
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi amitchandak V-lianl-msft  thanks for your suggestions.
    Those suggestions did help somewhat but not totally.
    I implemented the following steps to arrive at the output;
    1)Go to Query EDditor, duplicate the original table. Go to 'Choose Columns' and select columns which you want to use as a KEY to lookup. In my case they were 'Tab' and 'TabIndex'.
    2) Again Duplicate the original table and filter ParentTab for non-zero values.
    Merge this table with the Lookup table on 'Tab' from current table and 'ParentTab' from Lookup table.
    3)Select TabIndex from the expanded tab.
    4)Again duplicate the original table and filter 'ParentTab' for "00000000-0000-0000-0000-000000000000".
    5)Append both the tables.


     

3 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    1. Create a merged column in power query:

    2. Create a merge query in the same table:

    Expand the index column in the table to get the desired results.

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi amitchandak V-lianl-msft  thanks for your suggestions.
    Those suggestions did help somewhat but not totally.
    I implemented the following steps to arrive at the output;
    1)Go to Query EDditor, duplicate the original table. Go to 'Choose Columns' and select columns which you want to use as a KEY to lookup. In my case they were 'Tab' and 'TabIndex'.
    2) Again Duplicate the original table and filter ParentTab for non-zero values.
    Merge this table with the Lookup table on 'Tab' from current table and 'ParentTab' from Lookup table.
    3)Select TabIndex from the expanded tab.
    4)Again duplicate the original table and filter 'ParentTab' for "00000000-0000-0000-0000-000000000000".
    5)Append both the tables.