Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
In power query editor, I have to concatenate three of my columns which contains dates, numbers and letters.
It should work as the code is as simple as = [Column1]&[Column2]&[Column3]
However I get an issue message from power Bi ntfying me that the operator cannot be applied to number and date types.
I see that :
'Operator=&'
However in operator I only have simple text, and no null or "&" rows.
What does that mean ?
Thanks 🙂
Solved! Go to Solution.
The & operator only works on text values. You'll need to use converter functions to get text values from your non-text columns.
= [TextColumn] & Date.ToText([DateColumn]) & Number.ToText([NumberColumn])
Or
= [TextColumn] & Text.From([DateColumn]) & Text.From([NumberColumn])
Proud to be a Super User!
Hi,
I'm having this issue and the method below isn't working. I am trying to concatenate the following to create a unique ID;
Date in column (t1) as 10/31/2017 7:38:00 AM - Date/Time
Location ID in column (Location.id) as 2682 - Integer
Number.ToText for the location ID works but the formual returns as error when I add in Date.ToText.
Cheers.
The & operator only works on text values. You'll need to use converter functions to get text values from your non-text columns.
= [TextColumn] & Date.ToText([DateColumn]) & Number.ToText([NumberColumn])
Or
= [TextColumn] & Text.From([DateColumn]) & Text.From([NumberColumn])
Proud to be a Super User!
Hi,
I tried to use the function in a calcuated column in Direct Query mode. I got this error:
"Failed to resolve name 'Number.ToText'. It is not a valid table, variable, or function name."
Please advise.
There are a lot of query functions that are restricted in Direct Query mode. I believe that's one of them. You can't do much transformation of data in Direct Query mode, only in Import.
Proud to be a Super User!
Thank you for the reply. Is there a workaround?
I wanna create a column "Bay & Level" using PickAisle, PickModule, PickBay & PickLevel.
Eg: 42B-003-3. If the value is NULL, I want it to be blank.
I used the Number.ToText function to convert the PickAisle column to text values and combine it with PickModule. But, that didn't work in DirectQuery mode. Any help would be appreciated.
If you're using direct query, you should be working with a database that has all the data you need already pretty much modeled the way you need it. If there is a column like you're describing that doesn't exist I would recommend working with your DBA to adjust the source to suit your needs. If your database doesn't suit your reporting needs and can't be changed, you will probably need to use Import instead of Direct Query.
Proud to be a Super User!
Thanks it works perfectly !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |