Forum Discussion
Remove other columns
Hi kassymov_su ,
Very difficult to give an exact answer just by looking at your M code. I would really need to see the actual source data structure to be sure, but I think the following things are where you need to start:
1) You need a calendar dimension table. All of your ISODate_X calculations should be done in a separate table. If you build your calendar table in dataflows, for example, you only need to do it once and it will always be available to every report in future.
Reference:
https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/
2) You need to unpivot your fact table so that your dd.MM.yyyy column headers are in a single column. You can do this by multi-selecting any column that isn't a dd.MM.yyyy column, going to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.
Firstly, this makes it very easy to filter on this column in Power Query to only include your reporting period dates.
Secondly, it allows you to make a relationship to your calendar table as follows:
3) Relate your tables together: calendar[Date] ONE : MANY factTable[newDateColumn]. You can now use your calendar[ISODate_X] fields in visuals and it will correctly filter you fact table to the relevant data.
Reference:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
Hi kassymov_su ,
Very difficult to give an exact answer just by looking at your M code. I would really need to see the actual source data structure to be sure, but I think the following things are where you need to start:
1) You need a calendar dimension table. All of your ISODate_X calculations should be done in a separate table. If you build your calendar table in dataflows, for example, you only need to do it once and it will always be available to every report in future.
Reference:
https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/
2) You need to unpivot your fact table so that your dd.MM.yyyy column headers are in a single column. You can do this by multi-selecting any column that isn't a dd.MM.yyyy column, going to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.
Firstly, this makes it very easy to filter on this column in Power Query to only include your reporting period dates.
Secondly, it allows you to make a relationship to your calendar table as follows:
3) Relate your tables together: calendar[Date] ONE : MANY factTable[newDateColumn]. You can now use your calendar[ISODate_X] fields in visuals and it will correctly filter you fact table to the relevant data.
Reference:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
Thanks a lot Pete for the answer.
I have created a calendar table [Report Week] using Isoweek and using it as a drop down list to choose a reporting week.
When I choose a week I need from the drop down, there is another table below [WeekStartDate] that identify the week start date (vlookup). I loaded to query [WeekStartDate] table and using add.day created steps for following 6 days (so whenever I choose in the drop down a week I need, the Week start day changes and all the following 6 days also updates. I drilled down each date and named them as ISODate_d1, ISODate_d2 etc (I named them because if I reference to date then next week report will not find those names but if i refere to name then it's okay).
And then In my main Data I removed all the columns except (Columns I need, ISODate_d1, ISODate_d2...etc)
Source = #"Imported Excel Workbook"{[Item="Data",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Data,{"ID", "Name", "Start date", "Finish date", ISODate_d, ISODate_d1, ISODate_d2, ISODate_d3,ISODate_d4, ISODate_d5, ISODate_d6})It worked actually but I was wandering if there is another way to do it.
I was wandering if I can put a logic when removing colums.
I can create 2 steps "from" and "to" that will update whenever I choose a week from drop down. Then, is there a way to use them when removing colums like:
#"Removed Other Columns" = Table.SelectColumns if Columns in a Source table => from and <= to
Hi kassymov_su ,
As before, the best practice way to do this would be to unpivot your date columns (Point 2 above) then you can just apply a dynamic Table.SelectRows filter to the date column in Power Query. This also structures your data in the most efficient format for reporting.
If you want the range to be selected by the end user, then you would use a proper related calendar table to populate a slicer.
I think we're at the wrong end of an XY Problem here to be honest so, if you're able to share your source data and exactly what you want to achieve, we can look at getting the best overall solution for your scenario, otherwise we'll probably just go round in circles.
Pete
I agree with BA_Pete - I believe my post directly answers your question while his post provides the best solution for the end product.
