Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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
Super User

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.