Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
93 | |
87 | |
77 | |
72 | |
66 |
User | Count |
---|---|
115 | |
107 | |
86 | |
65 | |
64 |