Forum Discussion
Solved
How to Change Line Chart X-asix Month Language
Hi, I am developing a report where i am showing cost by months in a line Chart with months on X-Axis and cost on Y-Axis. Everything is working fine. My question is, how can i change the months...
When you create report in certain language version of Power BI, the automatic month and day names will always be in this language even if you open the report in different language version. There is no way to change this at the moment.
If you would not like to create the report again in German version of Power BI, there is way around this.
You need to create a separate Date-table that has the right kind of month names and then use this field as the X-axis. The date table can be easily created in the query editor.
1. Go to query-editor2. Select New Source and from there Blank Query3. Select Advanced Editor and copypaste this code on top of the default:let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table => let DayCount = Duration.Days(Duration.From(EndDate - StartDate)), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])), InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])), InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])), InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]), InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])), InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])), InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])), InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date) in InsertWeekEnding in CreateDateTable4. After you hit OK, there is a graphical interface where you tell where the calender should start and end. Importantly there is also a Culture-field that lets you decide the language for the month and day names in the calenderGerman = "De"5. Connect Date-table to your data-table by date and use the German month-field from the Date table in your picture.PS: Huge kudos to dkay84_PowerBI for pointing this method out.
When you create report in certain language version of Power BI, the automatic month and day names will always be in this language even if you open the report in different language version. There is no way to change this at the moment.
If you would not like to create the report again in German version of Power BI, there is way around this.
You need to create a separate Date-table that has the right kind of month names and then use this field as the X-axis. The date table can be easily created in the query editor.
1. Go to query-editor
2. Select New Source and from there Blank Query
3. Select Advanced Editor and copypaste this code on top of the default:
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable4. After you hit OK, there is a graphical interface where you tell where the calender should start and end. Importantly there is also a Culture-field that lets you decide the language for the month and day names in the calender
5. Connect Date-table to your data-table by date and use the German month-field from the Date table in your picture.
PS: Huge kudos to dkay84_PowerBI for pointing this method out.