cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Header as dates formating to MM/YYY format

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Header as dates formating to MM/YYY format

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. 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Community Support
Community Support

Re: Header as dates formating to MM/YYY format

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"

Capture2.JPG

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.

View solution in original post

2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Header as dates formating to MM/YYY format

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. 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Community Support
Community Support

Re: Header as dates formating to MM/YYY format

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"

Capture2.JPG

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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors