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.
I'm trying to create a multi column join, combining 2 columns , but the removal of leading zeroes is causing problems.
I tried using format but it gave me an error. I believe my problem is that the column I want to retain the leading zeroes on is the result of Max(column_name) . My table is grouped by booking_ID, getting max(column), and trying to use the result to merge booking_ID with max(seq) as a key to another table.
So my problem is without the leading zeroes, a one to one is turning into a many to many
12864 13
128641 3 are both coming out 1286413 creating many to many for the join.
When I do a format( max(seq), '000') I get an error.
Also changing the format to text doesn't add the leading zeroes.
thanks
Solved! Go to Solution.
Hi @Maryann,
You should use double quotes in above formula.
Column = CONCATENATE([Column1],FORMAT(Table1[Column2],"000"))
Best regards,
Yuliana Gu
Hi @Maryann,
You should use double quotes in above formula.
Column = CONCATENATE([Column1],FORMAT(Table1[Column2],"000"))
Best regards,
Yuliana Gu
Typically the trick with this is to remove the default "Change type" step that Power BI creates in the query and then do your type changes manually. This should revert the column in question to Text and preserve the leading zeros
can you give me directions on how to do this.
thanks
See attached PBIX.
When you are in Query Editor, over in Steps, just click the x next to the Changed Type step to remove it. This one already has that done.
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 |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |