Power Query: Reformatting DDMM to MMDD based on existing date-CodePudding

I'm trying to transform my data in Power Query so that they can be analysed by month. All data should be dated the 1st of the month, but a formatting change in the past has made some entries come up as "2/01/2022" instead of "1/02/2022" for February.

I normally would be able to do this in Excel but I would like to transform the data from the live database (SMSS) instead of using an export. So far, I can identify the rows that need changing but I keep getting errors when I try to use my IFs...

The step I'm stuck at:

#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Text Before Delimiter] <> "1" then Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US") else [Date])

If anyone knows how to use IF nicely in Power Query please let me know how the syntax here

CodePudding user response：

How about testing the day, and if it is not =1, swap month and day

#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Custom", each if Date.Day([Date])=1 then [Date] else #date(Date.Year([Date]),Date.Day([Date]),Date.Month([Date])))