locked
Dynamic remove power query columns RRS feed

  • Question

  • Hello

    I am working in power query in PowerBI

    My original data set looks like this

    Region      2018M01  2018M02  2018M03 ........2020M12

    A.             2             5              7         ...

    B.             ....

    C.             .....

    I would like to keep only the NEXT 6 months of data based on the earliest date in the dataset.. and Filter out all other Month columns

    I have tried first unpivoting the data set for the month and then linking the month names to actual dates like this

    2018M01 = 01/01/2018

    2018M02 = 01/02/2018 and so on

    This is done by extracting data from a calendar table and grouping them to create a unique date table related to each month.. 

    Then I import my calendar dates to my dataset and link to the months.....and  sort my data according to date from smallest to largest. 

    I group my data by date and then add an index. 

    I expand my data and then only keep the Index from 1...up until 6....SO basically the next 6 months,

    However since my original data table is quite big 500k rows, unpivoting them creates several millions of rows and then performing the above queries takes forever .....several hours. 

    Does anyone know of a better faster way to do the same thing ?

    thank you


    Monday, December 31, 2018 9:06 PM

Answers

  • I see. So if the months are not in order after appending new data, you can:

    1) Create a list of the table columns (minus the Region column) sorted in ascending order

    2) Use the list to reorder the table columns

    3) Select the first 7 columns

    4) Unpivot the month columns

    5) Merge with your date table

    Tuesday, January 1, 2019 1:28 AM

All replies

  • "I would like to keep only the NEXT 6 months of data based on the earliest date in the dataset.. and Filter out all other Month columns"

    I'm sure that I understand the statement. How is the next 6 months after the current six months determined? Is the earliest month from your original data set removed automatically every month?

    Monday, December 31, 2018 10:52 PM
  • After unpivoting my data, The next 6 months is basically the earliest month in the dataset + 6 months .

    So if 2018M01 is the earliest date in the dataset, then I keep everything from 2018M01 up until and including 2018 M07. This is done by linking these months to a DATES column that is coming from a calendar table. 



    Monday, December 31, 2018 10:58 PM
  • I'm obviously missing something, but given that your original data set already has the year/months in order, why can't you just select the first 7 columns from the dataset (region plus earliest six months), without having to unpivot, link with a date table, and so on?
    Monday, December 31, 2018 11:38 PM
  • I could do this, however each month the data is changing. That is each month I am bringing in a "similar" file, but the range of months is different. For example if our CURRENTDATE is in 2018M01, the file will have months ranging from 2018M01 up until 2020M01...........if our current date is in 2018M02 then the file will have months ranging from 2018M02 up until 2020M02 and so on. 

    Each month I am bringing in  a new file and adding it to the dataset. And in each of these files I only need 6 months of data. The reason I need to unpivot and link to a date table, is because when I apply DAX measures its much easier to work on a single month column rather than data across 6 different month columns. The date column link is applied for data visualisation purposes to plot it on graphs and apply time intelligence calcs.

    I would like to make this as automated and dynamic as possible. The unpivoting of the data helps with that. 


    Monday, December 31, 2018 11:48 PM
  • I see. So if the months are not in order after appending new data, you can:

    1) Create a list of the table columns (minus the Region column) sorted in ascending order

    2) Use the list to reorder the table columns

    3) Select the first 7 columns

    4) Unpivot the month columns

    5) Merge with your date table

    Tuesday, January 1, 2019 1:28 AM