Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Category | Month | Year |
X | 1 | 2018 |
X | 2 | 2018 |
X | 3 | 2018 |
X | 4 | 2018 |
X | 5 | 2018 |
X | 6 | 2018 |
X | 7 | 2018 |
X | 8 | 2018 |
X | 9 | 2018 |
X | 10 | 2018 |
X | 11 | 2018 |
X | 12 | 2018 |
Y | 1 | 2018 |
Y | 2 | 2018 |
Y | 3 | 2018 |
Y | 4 | 2018 |
Y | 5 | 2018 |
Y | 6 | 2018 |
Y | 7 | 2018 |
Y | 8 | 2018 |
Y | 9 | 2018 |
Y | 10 | 2018 |
Y | 11 | 2018 |
Y | 12 | 2018 |
Z | 1 | 2018 |
Z | 2 | 2018 |
Z | 3 | 2018 |
Z | 4 | 2018 |
Z | 5 | 2018 |
Z | 6 | 2018 |
Z | 7 | 2018 |
Z | 8 | 2018 |
Z | 9 | 2018 |
Z | 10 | 2018 |
Z | 11 | 2018 |
Z | 12 | 2018 |
X | 1 | 2019 |
X | 2 | 2019 |
X | 3 | 2019 |
X | 4 | 2019 |
X | 5 | 2019 |
X | 6 | 2019 |
X | 7 | 2019 |
X | 8 | 2019 |
X | 9 | 2019 |
X | 10 | 2019 |
X | 11 | 2019 |
X | 12 | 2019 |
Y | 1 | 2019 |
Y | 2 | 2019 |
Y | 3 | 2019 |
Y | 4 | 2019 |
Y | 5 | 2019 |
Y | 6 | 2019 |
Y | 7 | 2019 |
Y | 8 | 2019 |
Y | 9 | 2019 |
Y | 10 | 2019 |
Y | 11 | 2019 |
Y | 12 | 2019 |
Z | 1 | 2019 |
Z | 2 | 2019 |
Z | 3 | 2019 |
Z | 4 | 2019 |
Z | 5 | 2019 |
Z | 6 | 2019 |
Z | 7 | 2019 |
Z | 8 | 2019 |
Z | 9 | 2019 |
Z | 10 | 2019 |
Z | 11 | 2019 |
Z | 12 | 2019 |
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:
Category | Start Date | End Date |
X | 201801 | |
Y | 201802 | 201904 |
Z | 201903 |
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:
Category | Month | Year |
X | 1 | 2018 |
X | 2 | 2018 |
X | 3 | 2018 |
X | 4 | 2018 |
X | 5 | 2018 |
X | 6 | 2018 |
X | 7 | 2018 |
X | 8 | 2018 |
X | 9 | 2018 |
X | 10 | 2018 |
X | 11 | 2018 |
X | 12 | 2018 |
Y | 2 | 2018 |
Y | 3 | 2018 |
Y | 4 | 2018 |
Y | 5 | 2018 |
Y | 6 | 2018 |
Y | 7 | 2018 |
Y | 8 | 2018 |
Y | 9 | 2018 |
Y | 10 | 2018 |
Y | 11 | 2018 |
Y | 12 | 2018 |
X | 1 | 2019 |
X | 2 | 2019 |
X | 3 | 2019 |
X | 4 | 2019 |
X | 5 | 2019 |
X | 6 | 2019 |
X | 7 | 2019 |
X | 8 | 2019 |
X | 9 | 2019 |
X | 10 | 2019 |
X | 11 | 2019 |
X | 12 | 2019 |
Y | 1 | 2019 |
Y | 2 | 2019 |
Y | 3 | 2019 |
Y | 4 | 2019 |
Z | 3 | 2019 |
Z | 4 | 2019 |
Z | 5 | 2019 |
Z | 6 | 2019 |
Z | 7 | 2019 |
Z | 8 | 2019 |
Z | 9 | 2019 |
Z | 10 | 2019 |
Z | 11 | 2019 |
Z | 12 | 2019 |
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?
Solved! Go to Solution.
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.
Here is my sample pbix file: https://www.dropbox.com/s/pruk7994z5lvbfz/DateRows.pbix?dl=0
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.
Here is my sample pbix file: https://www.dropbox.com/s/pruk7994z5lvbfz/DateRows.pbix?dl=0
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |