Data Validation - PowerQuery

[neil37]

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!

[Anonymous]

Hi @neil37,

Did @jgordon11 's reply help you? Just create a blank query and then put the codes into Advanced editor.

If it did, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Stephen Tao

[neil37]

Hi @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)

[Anonymous · Accepted solution]

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.

[neil37]

Thank you, Stephen.  Changing the format from whole number to text was my issue after splitting via delimeter. Greatly appreciated. 

[jgordon11]

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
tbl