Changing format of date in power query

[redalert787]

hello everyone, 

I currently get my data from an external source that puts our sales data with the date info as "week.year". The problem with this is that the format sometimes ends up being for example "2.202" and my date table doesn't recognize this format. 

Someone gave me the advice of changing this format to "year-week" in power query by creating a new column. Can anyone help with this? What would be the easiest way to turn for example "3.2021" into "2021-3" in power query?

Thank you, 

[v-yingjl]

Hi @redalert787 ,

BA_Pete is right, since your column shows the value as 2.202 so the format is number, you need to change it to text to show it like 2.2020 to get the year value, then split by '.' and merge these two columns to get '2020-2'.

By the way, under this situation, the new column format is also text, if you want the date table recognize it, you have better to change the format to Date.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @redalert787 ,

There's a couple of things going on here.

Firstly, the fact that you end up with some values being "2.202" indicates that this field is being converted to a number data type by Power Query. Is this something you can remedy easily enough within your report so it gets fixed as a text value as soon as it's imported? This would simplify the rest of the requirement somewhat.

Pete

[BA_Pete · Accepted solution]

@redalert787 ,

Assuming that you CAN fix your month.year field as text, it's a really simple process to convert the format:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYzMjAyVIrViVayRDANjRBsExDTAKECwjQ0hLJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [oldDate = _t]),
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"oldDate", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Types", "oldDate", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"oldDate.1", "oldDate.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"oldDate.2", "oldDate.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"newDate")
in
    #"Merged Columns"

Summary:

1) Split old date column by delimiter "."

2) Merge resulting columns in order of year then month selection, specifying "-" as new delimiter

Pete