Forum Discussion

Kergofil's avatar
Kergofil
Regular Visitor
5 years ago

If statements - limitations

Hi .
I have a list in excel of more than 1200 post codes and the area they belong to (2 columns in the excel, one is the post code and the other is the area). I am using direct query in power bi and I have one column in my table ( Query) which is the post code only not the Area. Would it be possible to add a customize column with more than 1200 if statements writing them in M language so I can match the list I have with the post codes that I have with the name of the Area they belong to ? Like this I could create a column with the name of the Area... if this doesn’t work then do you have any other idea or solution?

Thank you in advance for your help. K

Need Help

7 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Kergofil 

    You can do a join (Merge Queries) on the 2 tables.  Pulling the Area Code into the Data table where you only have the Post Code.

    See this PBIX file for examples.

    If you can supply your data I can implement it for you.

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Kergofil's avatar
      Kergofil
      Regular Visitor
      5 years ago

      Hi Philip , 

       

      well actually I am using data straight forward connecting with the SQL server so I need my data to be dynamic and "live"... so I guess if I would use an excel table to create a new query and then merge it , then when I would try to share the report with other users that wouldnt be possible if I have my PC shut down...right? So, I was thinking to use maybe the M language to write many if statements or any other solution. I also tried to use customize column writing so many IF statements but in the end the message I got was "this query is too complicated to run"... I think there is a limitation also here on the number of IF statements... 😞  If any other solution or idea exists please share. Thank you again

      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        5 years ago

        Hi Kergofil 

        So you are loading data from SQL Server running on your PC?

        Whether you use a lot of if statements in a custom column, or have 2 tables and merge them, you are still getting data from the same place and subject to that data being available.

        Regards

        Phil

        If I answered your question please mark my post as the solution.
        If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.