If statement with multiple conditions in Power Query

[Guinaba]

Hi guys,

I am trying to create the Index_MthYear column based on the MthYear col. below pic. So I have created a costum column with the following code, but it doesn't work. Any suggestiion?

if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],0) then 1 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],1) then 2 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],2) then 3 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],3) then 4 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],4) then 5 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],5) then 6 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],6) then 7 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],7) then 8 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],8) then 9 else
if [#"Mth/Year"]=Date.AddMonths([#"Mth/Year"],9) then 10
else 99

MthYear	Index_MthYear
1/06/2020	1
1/07/2020	2
1/08/2020	3
1/09/2020	4
1/10/2020	5
1/11/2020	6
1/12/2020	7
1/01/2021	8
1/02/2021	9

 

[GraH]

Hi,

Why are you writing an if-statement? Using the Index starting at 1 does not work?

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MthYear", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

 

[Guinaba]

Hi @GraH,

I tried to add a index column but the problem is I have repeated rows in the MthYear col. and the index column should respect that for example:

MthYear Ind

1/06/2020 1
1/06/2020 1
1/06/2020 1
1/07/2020 2
1/07/2020 2...

...but that doesn't happen

 

[GraH]

Perhaps like this
- Group By On date, aggregate All Rows (call it All)
- Set Index
- Expand All again

Depending on situation, better would be to create a date table from scratch, something like

let
    Date = #table(1,{{#date(2020,6,1)}}),
    DateAsColName = Table.RenameColumns(Date,{{"Column1", "Date"}}),
    #"Inserted End of Year" = Table.AddColumn(DateAsColName, "End of Year", each Date.EndOfYear([Date]), type date),
    AddCol_EOY_NextYear = Table.AddColumn(#"Inserted End of Year", "EOY Next Yr", each Date.AddMonths([End of Year],12), type date),
    DatesAsNumber = Table.TransformColumnTypes(AddCol_EOY_NextYear,{{"Date", Int64.Type}, {"End of Year", Int64.Type}, {"EOY Next Yr", Int64.Type}}),
    ListOfDates = Table.AddColumn(DatesAsNumber, "Dates", each {[Date]..[EOY Next Yr]}),
    KeepDateColOnly = Table.SelectColumns(ListOfDates,{"Dates"}),
    #"Expanded Date" = Table.ExpandListColumn(KeepDateColOnly, "Dates"),
    DateAsDate = Table.TransformColumnTypes(#"Expanded Date",{{"Dates", type date}}),
    #"Added Index" = Table.AddIndexColumn(DateAsDate, "Index", 1, 1) 
in
    #"Added Index"

 

[Guinaba]

Hi @GraH,

Have found a way creating a custom col and using this functions:

if #date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1)=[#"Mth/Year"] then 1 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),1)=[#"Mth/Year"] then 2 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),2)=[#"Mth/Year"] then 3 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),3)=[#"Mth/Year"] then 4 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),4)=[#"Mth/Year"] then 5 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),5)=[#"Mth/Year"] then 6 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),6)=[#"Mth/Year"] then 7 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),7)=[#"Mth/Year"] then 8 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),8)=[#"Mth/Year"] then 9 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),9)=[#"Mth/Year"] then 10 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),10)=[#"Mth/Year"] then 11 else if
Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),11)=[#"Mth/Year"] then 12 else 13

 

[GraH]

Hi Guinaba, thanks, I know it can be done like that
Still a lot of code for something simple, using 24x DateTime.LocalNow(). It is not optimal.

This is a UI solution (suggested in #4, using the group by)

let
    Source = Excel.CurrentWorkbook(){[Name="tDates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MthYear", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"MthYear"}, {{"All", each _, type table [MthYear=date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"MthYear"}, {"MthYear.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"MthYear.1"})
in
    #"Removed Columns"

 

[Guinaba]

Hi @GraH,

Thanks for your feedback, have tried you code adapting to my query (screenshot) but not sure where I am making a mistake, could you please help me out to use your code.

let
    Source = Excel.CurrentWorkbook(){[Name="tDates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstDayWeek", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FirstDayWeek"}, {{"All", each _, type table [FirstDayWeek=date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"FirstDayWeek"}, {"FirstDayWeek.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"FirstDayWeek.1"})
in

I need to index respecting the FirstDayWeek column for example:

FirstDayWeek	IndexWeek
29/06/2020	1
29/06/2020	1
29/06/2020	1
06/07/2020	2
13/07/2020	3
13/07/2020	3

Kind Regards,

Gilly

 

[GraH]

Hi Gilly,

Aha, I thought there was a catch.

I need to index respecting the FirstDayWeek column for example:

let
    //Replace this with your source
    Source = Excel.CurrentWorkbook(){[Name="q4_Report_WK"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AvProd", type date}, {"Supplier", type text}}),
   //Do all your transformations required
   //Here add the weekstart (depending if week starts at Sun or Mon tweak each Date.StartOfWeek([AvProd], 1)  Add the "1" for "Mon"
    #"Inserted Start of Week" = Table.AddColumn(#"Changed Type", "Start of Week", each Date.StartOfWeek([AvProd], 1)),
    //Group on that date
    #"Grouped Rows" = Table.Group(#"Inserted Start of Week", {"Start of Week"}, {{"All", each _, type table [AvProd=date, Supplier=text, Start of Week=date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"AvProd", "Supplier"}, {"AvProd", "Supplier"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Start of Week"})
in
    #"Removed Columns"

 

[Guinaba]

Thanks @GraH1! Much appreciate it!

 

[GraH]

Glad I could help. Thanks for the follow up.