undefined

[Fraizerrrrr]

How to add a custom column for StartDate on power query but it's like this. Because on the source there is only the month column.

[MFelix · Accepted solution]

Hi @Fraizerrrrr ,

Try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUXJ08vH0czUwVIrVQRczgomZY1FnjqIuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Line = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Line", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #date([Month],1,1), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Added Custom", "End of Year", each Date.EndOfYear([Custom]), type date),
    #"Added Custom1" = Table.AddColumn(#"Inserted End of Year", "Custom.1", each {Number.From([Custom])..Number.From([End of Year])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}),
    #"Inserted Start of Week" = Table.AddColumn(#"Changed Type1", "Start of Week", each Date.StartOfWeek([Custom.1]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Custom.1]), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted End of Week",{"Month", "Custom", "End of Year", "Custom.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Duplicates", "Merged", each Text.Combine({Text.From([Start of Week], "en-GB"), Text.From([End of Week], "en-GB")}, " to "), type text)
in
    #"Inserted Merged Column"

This may need some adjustments based on your model.