The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello all,
I have a column with site IDs that are filled with: 1, 2, 3, 4, 10, 11, 12, A, AB, and B.
Only the sites with numbers are "Construction" sites (the alphabetic site IDs are a different kind of site).
I am creating a custom column that will re-write these IDs to match another table I need to merge onto using that column.
Then the column would be filled in with: Site 01, Site 02, Site 03, Site 04, Site 10, Site 11, Site 12, Site A, Site AB, and Site B.
I wrote this text in the Custom Column editor, but it is not adding a 0 in front of the single digit numbers:
if [Site Type] = "Construction"
then Text.Combine({"Site ", Text.PadStart(Text.From([#"Site #"], "en-US"), 2, "0")})
else Text.Combine({"Site ", Text.PadStart(Text.From([#"Site #"], "en-US"), 1)})
Can anyone point me to where I've gone wrong? Thank you!!
P.S. If I'm going in the wrong direction, and you can re-write this not to be based on Construction, but just a way that always adds a 0 to single digit numbers (not letters or double-digit numbers), then that would be great too!
Solved! Go to Solution.
@alicek - I attempted as:
= Table.AddColumn(#"Added Custom", "Custom1", each Text.Combine({"Site ", if Value.Is(Value.FromText([siteID]), type number) then Text.PadStart([siteID],2,"0") else [siteID]}))
Used → https://stackoverflow.com/a/39736101
Proud to be a Super User!
@alicek - I attempted as:
= Table.AddColumn(#"Added Custom", "Custom1", each Text.Combine({"Site ", if Value.Is(Value.FromText([siteID]), type number) then Text.PadStart([siteID],2,"0") else [siteID]}))
Used → https://stackoverflow.com/a/39736101
Proud to be a Super User!
It looks alright to me.
Debug it by cutting it right down. Try adding a column with just
Text.PadStart(Text.From([#"Site #"], "en-US"), 2, "0")
If that works, add in the Text.Combine, then the 'if'statement
Hi @alicek
This code will work. Here's a sample PBIX file.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKy4pKk0uyczPU9JRMlSK1YlW8k9Ly0xOhXPR1BihqjHCpsYYVY0xNjUmqGpMsKkxNEBzkAE2VY6oihydiFCEVQ1QMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Site Type" = _t, #"Site ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Site Type", type text}, {"Site ID", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Site Type] = "Construction" and List.ContainsAny(Text.ToList([Site ID]), { "0" .. "9" }) then Text.Combine( {"Site ",Text.PadStart([Site ID],2,"0")}) else if [Site Type] = "Construction" then Text.Combine( {"Site ", [Site ID]} ) else [Site ID])
in
#"Added Custom"
To give you this
I wasn't sure of your table structure and column names but if you have trouble adapting this to your situation let me know and I can help further.
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.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
101 | |
101 | |
72 | |
58 |
User | Count |
---|---|
263 | |
121 | |
114 | |
93 | |
85 |