Forum Discussion
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.
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)
2 Replies
- edhansCommunity 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)
- InukBotNew Member
Thank you very much for the help!