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

Need help creating custom columns based on active period i.e. start date and end date (M)

Hi all, 

 

I have a really tough problem. Would appreciate the help I can get! 

 

I have the following input list/table in an Excel file: 

 

Category
X
Y
Z

 

I need these categories to be one one row per month for a given time period. I use the code below to add month number and year.

 

 

    #"Converted to Table" = Table.FromList(#"Account Number1", Splitter.SplitByNothing(), null, null, ExtraValues.Error,
    #"Removed Duplicates" = Table.Distinct(#"Converted to Table"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "MonthNo", each {01..12}),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "MonthNo"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Month", "Year", each {2018..2019}),
    #"Expanded Year" = Table.ExpandListColumn(#"Added Custom1", "Year"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Year", "Month", each Text.Start( Date.MonthName( #date([Year], [MonthNo], 1)), 3))

 

 

My table then looks like this:

 

CategoryMonthYear
X12018
X22018
X32018
X42018
X52018
X62018
X72018
X82018
X92018
X102018
X112018
X122018
Y12018
Y22018
Y32018
Y42018
Y52018
Y62018
Y72018
Y82018
Y92018
Y102018
Y112018
Y122018
Z12018
Z22018
Z32018
Z42018
Z52018
Z62018
Z72018
Z82018
Z92018
Z102018
Z112018
Z122018
X12019
X22019
X32019
X42019
X52019
X62019
X72019
X82019
X92019
X102019
X112019
X122019
Y12019
Y22019
Y32019
Y42019
Y52019
Y62019
Y72019
Y82019
Y92019
Y102019
Y112019
Y122019
Z12019
Z22019
Z32019
Z42019
Z52019
Z62019
Z72019
Z82019
Z92019
Z102019
Z112019
Z122019

 

Perfect so far!

 

However, I den find out that there is some crucial information missing from the first input table. It actually looks like this: 

 

CategoryStart DateEnd Date
X201801 
Y201802201904
Z201903 

 

Category X,Y,Z are only active in certain time periods.. I need to create a table that takes this into concideration but I don't know how... I would like my table to look like this:

 

CategoryMonthYear
X12018
X22018
X32018
X42018
X52018
X62018
X72018
X82018
X92018
X102018
X112018
X122018
Y22018
Y32018
Y42018
Y52018
Y62018
Y72018
Y82018
Y92018
Y102018
Y112018
Y122018
X12019
X22019
X32019
X42019
X52019
X62019
X72019
X82019
X92019
X102019
X112019
X122019
Y12019
Y22019
Y32019
Y42019
Z32019
Z42019
Z52019
Z62019
Z72019
Z82019
Z92019
Z102019
Z112019
Z122019

 

As you see, the table has category X for all months all years because it is active the whole period, while Y only has rows between 201802 and 201904 and Z only after 201903... 

 

Could anyone help me with the M code to create this table?

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @Anonymous ,

If you bring in the Start Date and End Date you can build a column to do a compare to determine which rows to remove.

daterows.jpg

Here is my sample pbix file:  https://www.dropbox.com/s/pruk7994z5lvbfz/DateRows.pbix?dl=0

 

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

Hello @Anonymous ,

If you bring in the Start Date and End Date you can build a column to do a compare to determine which rows to remove.

daterows.jpg

Here is my sample pbix file:  https://www.dropbox.com/s/pruk7994z5lvbfz/DateRows.pbix?dl=0

 

Anonymous
Not applicable

Hi @jdbuchanan71 , this worked, thanks a lot

 

It's not the perfect solution (I think), because I would like the function to only add column month and year for the active accounts only. Now it add month and year for all accounts for all years and months and then after it deletes the unwanted rows. This seems less optimal. 

 

Do you have a suggestion for how it could be done so that it only add for active periods initially? 

 

If not, still good, just want to explore possible alternatives

Hello @Anonymous ,

I don't know of way to do it how you describe but I am not as well versed in M (PowerQuery) as some of the other users here on the boards.

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.