Remove other columns

[kassymov_su]

HI All,

I have a data with Quantities by day for the hole period of project (each column name is a day dd.mm.yyyy). For the weekly report I want to delete other columns and leave only the reporting period ones.  What is the proper way to do that? The way I've done is a bit strange but it works. 

let
Date = Excel.CurrentWorkbook(){[Name="DPRDate12"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Date,{{"Week start date", type date}}),
Column1 = #"Changed Type"{0}[Week start date],
ISODate_d = Date.ToText(#"Column1", "dd.MM.yyyy"),
ISODate_1 = Date.AddDays(#"Column1",1),
ISODate_d1 = Date.ToText(#"ISODate_1", "dd.MM.yyyy"),
ISODate_2 = Date.AddDays(#"Column1",2),
ISODate_d2 = Date.ToText(#"ISODate_2", "dd.MM.yyyy"),
ISODate_3 = Date.AddDays(#"Column1",3),
ISODate_d3 = Date.ToText(#"ISODate_3", "dd.MM.yyyy"),
ISODate_4 = Date.AddDays(#"Column1",4),
ISODate_d4 = Date.ToText(#"ISODate_4", "dd.MM.yyyy"),
ISODate_5 = Date.AddDays(#"Column1",5),
ISODate_d5 = Date.ToText(#"ISODate_5", "dd.MM.yyyy"),
ISODate_6 = Date.AddDays(#"Column1",6),
ISODate_d6 = Date.ToText(#"ISODate_6", "dd.MM.yyyy"),

Source = SharePoint.Files("https://ismagulovttbigroup.sharepoint.com/sites/International", [ApiVersion = 15]),
#"Опер справка_1 xlsx_https://ismagulovttbigroup sharepoint com/sites/International/Shared Documents/Хромтау ДОФ/Опер Учет/Форма для Линии/" = Source{[Name="Опер справка_1.xlsx",#"Folder Path"="https://ismagulovttbigroup.sharepoint.com/sites/International/Shared Documents/Хромтау ДОФ/Опер Учет/Форма для Линии/"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"Опер справка_1 xlsx_https://ismagulovttbigroup sharepoint com/sites/International/Shared Documents/Хромтау ДОФ/Опер Учет/Форма для Линии/"),
Опер_Справка_Table = #"Imported Excel Workbook"{[Item="Опер_Справка",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Опер_Справка_Table,{"ID", "Наименование участка", "Длительность ", "План #(lf)Начало", "План #(lf)Завершение", "Факт#(lf)Начало", "Факт#(lf)Завершение", "Трудо затраты", "Ед.#(lf)Измерения", "Плановый физ.объем РД", "Накопительная #(lf)физ.объем (Факт)", "Перевыполнение", "Физ. Прогресс", ISODate_d, ISODate_d1, ISODate_d2, ISODate_d3,ISODate_d4, ISODate_d5, ISODate_d6}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Transposed Table1" = Table.Transpose(#"Transposed Table"),
#"Added Custom" = Table.AddColumn(#"Transposed Table1", "Факт за неделю", each List.Sum({[Column14],[Column15],[Column16],[Column17],[Column18],[Column19],[Column20]})),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"ID", "Трудо затраты"}, #"Added Custom", {"Column1", "Column8"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Факт за неделю"}, {"Факт за неделю"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Added Custom",{"ID", "Наименование участка", "Ед.#(lf)Измерения", "Плановый физ.объем РД", "Накопительная #(lf)физ.объем (Факт)", "Перевыполнение", "Факт за неделю", "Физ. Прогресс"})
in
#"Removed Other Columns1"

[jennratten]

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

[BA_Pete · Accepted solution]

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

[kassymov_su]

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 

[BA_Pete]

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

[jennratten]

I agree with @BA_Pete - I believe my post directly answers your question while his post provides the best solution for the end product.