Forum Discussion

neil37's avatar
neil37
Advocate I
3 years ago
Solved

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 r...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

jgordon11's avatar
jgordon11
Resolver II
3 years ago

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