cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marekmarek
Post Patron
Post Patron

Date formatting text to date

Hi All

 

Background:

I am importing a folder with multiple files with exactly the same data structure in each of them. Files have ".xlsx" format. In Power BI I do have a column called "Source.Name" which is the name of each file (012018_6120; 022018,6120, etc.). First two characters stand for a month number.

 

Need:

1. I would like to have first two characters from column "Source.Name" transformed into "MMM" format (Jan, Feb, Mar, Apr, etc.).

2. I would like to have this in a date format, because later I will leverage those months in some visualizations.

 

Summary:

Before: "012018_6120.xlsx"

After: "Jan" [date format]

 

thanks!

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Here is how I would do this, all in Power Query before bringing it in to Power BI's data model:

  • Create a data table, and ensure it has a month column and a month name column. 

It might look something like this in Power Query's advanced editor:

let
    Source = {Number.From(#date(2019,1,1))..Number.From(#date(2019,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text)
in
    #"Inserted Month Name"

That last step will convert January to "Jan" for you, just showing 3 letters instead of the full month name.

  • In your data table, if "012018_6120.xlsx" is in the FileName field, use this in a new column:
    • Number.From(Text.Start([FileName],2))
    • Change that column type to an integer/whole number.
  • Import both the date table and the data table with file names into your model.
  • Mark the date table as a Dates Table, using the Date field.
  • Releate the Month field in the date table to the Month field in your Data table. It would look like this:
    • image.png

For any visual or measures, use the Month field in the Dates table and it will automatically do any date intelligence correctly, and correctly pull related fields from your data table, which is Table1 in my example.



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

4 REPLIES 4
edhans
Super User III
Super User III

Here is how I would do this, all in Power Query before bringing it in to Power BI's data model:

  • Create a data table, and ensure it has a month column and a month name column. 

It might look something like this in Power Query's advanced editor:

let
    Source = {Number.From(#date(2019,1,1))..Number.From(#date(2019,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text)
in
    #"Inserted Month Name"

That last step will convert January to "Jan" for you, just showing 3 letters instead of the full month name.

  • In your data table, if "012018_6120.xlsx" is in the FileName field, use this in a new column:
    • Number.From(Text.Start([FileName],2))
    • Change that column type to an integer/whole number.
  • Import both the date table and the data table with file names into your model.
  • Mark the date table as a Dates Table, using the Date field.
  • Releate the Month field in the date table to the Month field in your Data table. It would look like this:
    • image.png

For any visual or measures, use the Month field in the Dates table and it will automatically do any date intelligence correctly, and correctly pull related fields from your data table, which is Table1 in my example.



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

it works - thank you @edhans 

Great to hear!



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

Sorry, I'm still a little struggling with another dataset... 

 

I have two tables - Table A and Table B. They are connected by fields:

* Dates_Everywhere (Table A)

* Tired_Of_Dates (Table B)

 

However, when I create a chart with "Dates_Everywhere" axis, it is not filtered chronologically... any idea why?Data_ModelData_Model

 

Table A_2Table A_2RelationshipRelationship

Table ATable ATable BTable B

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors