Forum Discussion
Data Validation - PowerQuery
Hello,
I need some assistance in PowerQuery in ensuring there are 8 digits following a hyphen (i.e., 2022-########). The data comes in as 2022-2345; 2022-354; 2022-875437, etc. I need it to be reformatted to: 2022-00002345; 2022-00000354; 2022-0875437.
Essentially, I just need to add zeros on the front end of the data (after the hyphen) to ensure the data matches another source for proper modeling relationships.
| Actual | Desired |
| 2022-4876 | 2022-00004876 |
| 2022-464759 | 2022-00464759 |
| 2022-123 | 2022-00000123 |
Thank you!
Hi neil37 ,
I made a pbix file myself, which you can download and view later from the attachment.
Here're the steps in detail.
1.Duplicate Actual column.
2.Split the duplicated column with "-".
3.Change the types of the splited columns as text.
4.Add a custom column so that the number on the right half is added to 0 and combined with the number on the left half by "-".
5.After the unneeded columns are removed, the result is as follows.
Hopefully, the detailed steps above and the accessories will help you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
"i45WMjIwMtI1NDJWitVBcExQeaZm5hZKsbEA", BinaryEncoding.Base64), Compression.Deflate))),
tbl = Table.TransformColumns(Source, {{"Column1", each let ts = Text.Split(_,"-") in ts{0} & "-" & Text.PadStart(ts{1}, 8, "0")}})
in
tblHi jgordon11 & Anonymous ,
I imagine the code would work if I was starting from a blank query not connected to a specific source already? This specific task is for a source that is ingested already via an active query. I am a bit new to this language and do not see how I add this to an active query from advanced editor (i.e., which source goes where, where column names go)
Hi neil37 ,
I made a pbix file myself, which you can download and view later from the attachment.
Here're the steps in detail.
1.Duplicate Actual column.
2.Split the duplicated column with "-".
3.Change the types of the splited columns as text.
4.Add a custom column so that the number on the right half is added to 0 and combined with the number on the left half by "-".
5.After the unneeded columns are removed, the result is as follows.
Hopefully, the detailed steps above and the accessories will help you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
