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 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!
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 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |