Power query column editing

Question

I have a table in power bi query with dates

01.01.2020

02.01.2020

and so on..

I need to duplicate this table and replace values 01.01.2020 into 20200101 and so on. Is there an obvious, easy way for this?

Answer 1

First option:

Here is the simplest option I found:

• Create a custom column and apply "Text.Reverse" to your column
• Create a custom column and apply to the newly created "Text.Remove" for "." which will remove the "." of your string.

Here is what you will get, with "reverse date" as your column in the reverse order, and "reverse date without point" as the second column without the point.

Here is the M code:

#"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "reverse date", each Text.Reverse([Date])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "reverse date witout point", each Text.Remove([reverse date], {"."}))

Second option:

Here is a second option, which is longer:

• Break down your column in three distinct columns with "." as delimiter
• Add new columns with padding zero to day and months (I called them "month with zero" and "day with zero")
• Concatenate
• and you get you result!

Here is my starting point:

Here is the first step, "breaking the column" in "columns":

Here is the custom column with zero padding:

Here is how you concatenate:

Here is the M code:

 #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Date", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date.1", type text}, {"Date.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date.1", "Day"}, {"Date.2", "Month"}, {"Date.3", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month with zero", each Text.PadStart(Text.From([Month]),2,"0")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Day with zero", each Text.PadStart(Text.From([Day]),2,"0")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Day", "Month"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Year", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "New Date", each [Year] & [Month with zero] & [Day with zero])
in
#"Added Custom1"