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
Hello! This is how you can remove all columns in a table other than those whose column names are dates.
BEFORE
AFTER
SCRIPT
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOtFKRkAWAoNEjIEsBI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [blue = _t, column1 = _t, #"01/1/2022" = _t, #"02/1/2022" = _t]),
SelectColumnsWithDatesAsNames = Table.SelectColumns ( Source, List.Select ( Table.ColumnNames (Source), each Value.Is(Value.FromText( _ ), type date ) ) )
in
SelectColumnsWithDatesAsNames