Forum Discussion

InukBot's avatar
InukBot
New Member
3 years ago
Solved

Power Query-editor and i want to avoid using import mode

First of all, sorry for the bad english, not my first language.

I am very new to Power BI and I want to make a map that devides Denmark op into the regions. I was told to use Power Query-editor to get a column that says the name of the region, but i was told not to use import mode to help

I have used this to make sure that it will divide the regoins by using the postalcodes. the code I have come up with so far is this:

 

let changeZero = if [PostalCode] = "null" or [PostalCode] = null then "0" else [PostalCode],

postalCode = Number.FromText(changeZero)

in

if postalCode >= 1000 and postalCode <= 2999 then "Storkøbenhavn"

else if postalCode >= 3000 and postalCode <= 3699 then "Nordsjælland"

else if postalCode >= 4000 and postalCode <= 4999 then "Vest/sydsjælland"

else if postalCode >= 5000 and postalCode <= 5999 then "Fyn"

else if postalCode >= 6000 and postalCode <= 6799 then "Sønderjylland"

else if postalCode >= 6800 and postalCode <= 7699 then "Storkøbenhavn"

else if postalCode >= 7700 and postalCode <= 7999 then "Nordjylland"

else if postalCode >= 8000 and postalCode <= 8999 then "Østjylland"

else if postalCode >= 9000 and postalCode <= 9999 then "Nordjylland"

else "Ikke i Danmark"

 

Many thanks.

2 Replies

  • edhans's avatar
    edhans
    Community Champion

    You cannot do it with Number.FromText. That won't fold and only functions that fold can be used with direct import. Off the top of my head I am not aware of a way to convert text to a number and it still fold. Can you have a view written on your server that does that for you first?

     

    For reference, here is a good list of functions and whether or not they fold, which is absolutely necessary for a model that uses Direct Query. The (Almost) Definitive Guide to Query Folding - It's Not About The Cell (itsnotaboutthecell.com)