If statements - limitations

[Kergofil]

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

[PhilipTreacy]

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]

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]

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.

[Kergofil]

Yes Phil,

Thats correct. I am loading data from SQL server where there I have only a column with the post code (table A). On the other, I have an excel workbook in my PC where I have post code with the Area name (table B). So merging these two tables it would work fine but only when I keep on my PC other users when sharing the report could see the results. So am looking if there is another way to find a solution... Thanks again

[PhilipTreacy]

Hi @Kergofil 

Just so I understand this correctly, you have SQL Server running on your PC in Direct Query mode and you are only loading 1 column from it that contains the post code?  Or you are laoding a lot more data/columns than that, but 1 of those columns is the post code?

Is Direct Query necessary?  You state that you need your data to be live in which case you either need to leave your PC on all the time or you move the SQL DB and Excel file to the cloud/some other always on location.

Also, if your data is stored on your PC, you may need to set up a gateway to allow reports to be shared on PBI Service.

Seems like there are 2 issues here: live access to the data because of Direct Query, and then the need to join the tables.

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]

Hi

As you state, I am loading more data and one of the columns I download is the Postcode. Direct Quey unfortunately is necessary in my case. 

Yes indeed there is a gateway installed and therefore I can easily share my report in Power BI service. 

[PhilipTreacy]

Hi @Kergofil 

OK so given all of that, then just do the table join ?

Or add a new column to the SQL data for the Area Code so the join doesn't have to happen?

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.