Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Here is how I would do this, all in Power Query before bringing it in to Power BI's data model:
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.
Number.From(Text.Start([FileName],2))
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere is how I would do this, all in Power Query before bringing it in to Power BI's data model:
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.
Number.From(Text.Start([FileName],2))
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat to hear!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry, 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?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |