new message

[Anonymous]

Hi,

I have a date filter created in a spreadsheet which I am using to connect multiple data sets together through one date slicer in pbix. In the spreadsheet, the order, for example, goes:

1 2017

2 2017

3 2017

However in Power Query it recognises the values as:

01/01/2017

01/02/2017

01/03/2017

When visualising data, this therefore displays data on the first day of the month, and none of the of the other days within that month.

Is there a way to convert the column of dates within Power Query, maybe via text, to just display jan-2017, feb-2017 so it is a sum of the values as a whole for that month?

Is this possible?

Thanks

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

I reproduce your scenario and get expected result. Please follow the steps below.

1. I create sample table in excel with Text format.

sample table in spreadsheet
2. Inport it to Power BI, it recognises the values as date type, please cancel the "Changed type" step in Power Query.

3. Using this structure, we could write a CHOOSE function named fnChoose_CustCode for our scenario as follows:

fnChoose_CustCode = (input) => let
values = {
{"1","Jan"},
{"2","Feb"},
{"3","Mar"},
{"4","Apr"},
{"5","May"},
{"6","Jun"},
{"7","Jul"},
{"8","Aug"},
{"9","Sep"},
{"10","Doc"},
{"11","Nov"},
{"12","Dec"},
{input,"Undefined"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,

Click View –> Advanced Editor. Let’s paste in our code of function just before the Source = line.

4. Split the Date column into two parts, month and year, then use the New function to transfer month to Jan,Feb etc format. Then combine the Month and Year column by adding a custom colum. 

5. Finally, remove the other columns you don't need. Click "Apply" in Power Query. You will get right result as follows.

Exact result

The below is my Power Query Statement, please review it.

let
    
fnChoose_CustCode = (input) => let
values = {
{"1","Jan"},
{"2","Feb"},
{"3","Mar"},
{"4","Apr"},
{"5","May"},
{"6","Jun"},
{"7","Jul"},
{"8","Aug"},
{"9","Sep"},
{"10","Doc"},
{"11","Nov"},
{"12","Dec"},
{input,"Undefined"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,


Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Combine({fnChoose_CustCode([Date.1]),"-",[Date.2]},"")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date.1", "Date.2"})
in
    #"Removed Columns"

Best Regards,
Angelia