Header as dates formating to MM/YYY format

[Zaibass81]

Hello,

 i am importing table where header is a date e.g 30/09/20 is there a way to change date format in the Header to MM/YYY format?

Thanks 

[edhans · Accepted solution]

You can, but I'd caution against it, which I'll go into in a second. To do this, create a new blank query in Power Query and in the Advanced Editor (home ribbon), get rid of everything there and paste this in:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTK0BBGGEFasTrRSEohpBCSMDBCCySCmOVzQFCyYAmIawAkLsGAqTDvEYDOwYBpEHkYYgwUzYGYCjQObERsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"30/9/20" = _t, #"31/8/20" = _t, #"31/7/20" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Data"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "New Date", each #date(
    Number.FromText(Text.AfterDelimiter([Attribute],"/",1))+2000,
    Number.FromText(Text.BetweenDelimiters([Attribute],"/","/",0)),
    Number.FromText(Text.BeforeDelimiter([Attribute],"/",0))
    )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"New Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"New Date", type text}}, "en-US")[#"New Date"]), "New Date", "Value")
in
    #"Pivoted Column"

If you have a lot of data, this may not work as pivoting columns works better when you aggregate.

The other reason you don't want to is you want the data to be normalized, which means your dates are in one column and your data is in another, not your data spread out across multiple date columns. Your model will be very difficult to work with if your data is in multiple columns.

If you want to see how that works, just delete the last step of my query above to leave the data in a normalized format. 

[v-juanli-msft · Accepted solution]

Hi @Zaibass81 

edhans's solution would be helpful, please copy the code into your Advanced editor.

"i am importing table where header is a date e.g 30/09/20 is there a way to change date format in the Header to MM/YYY format?"

If date header like 30/09/20 means September in 2020 and your final result is September 2020, you could try the following codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTK0BBGGEFasTrRSEohpBCSMDBCCySCmOVzQFCyYAmIawAkLsGAqTDvEYDOwYBpEHkYYgwUzYGYCjQObERsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"30/9/20" = _t, #"31/8/20" = _t, #"31/7/20" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Data"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "New Date", each #date(
    Number.FromText(Text.AfterDelimiter([Attribute],"/",1))+2000,
    Number.FromText(Text.BetweenDelimiters([Attribute],"/","/",0)),
    Number.FromText(Text.BeforeDelimiter([Attribute],"/",0))
    )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Combine({Number.ToText(Date.Month([New Date])),Number.ToText(Date.Year([New Date]))},"/")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "New Date"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

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

[edhans · Accepted solution]

You can, but I'd caution against it, which I'll go into in a second. To do this, create a new blank query in Power Query and in the Advanced Editor (home ribbon), get rid of everything there and paste this in:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTK0BBGGEFasTrRSEohpBCSMDBCCySCmOVzQFCyYAmIawAkLsGAqTDvEYDOwYBpEHkYYgwUzYGYCjQObERsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"30/9/20" = _t, #"31/8/20" = _t, #"31/7/20" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Data"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "New Date", each #date(
    Number.FromText(Text.AfterDelimiter([Attribute],"/",1))+2000,
    Number.FromText(Text.BetweenDelimiters([Attribute],"/","/",0)),
    Number.FromText(Text.BeforeDelimiter([Attribute],"/",0))
    )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"New Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"New Date", type text}}, "en-US")[#"New Date"]), "New Date", "Value")
in
    #"Pivoted Column"

If you have a lot of data, this may not work as pivoting columns works better when you aggregate.

The other reason you don't want to is you want the data to be normalized, which means your dates are in one column and your data is in another, not your data spread out across multiple date columns. Your model will be very difficult to work with if your data is in multiple columns.

If you want to see how that works, just delete the last step of my query above to leave the data in a normalized format.