Anonymous's avatar
Anonymous
Not applicable
5 years ago
Status:
New

How do I calculate a specific cell in Power Query in an imported excel sheet?

Hi,

 

In my source file I have several items with different "Occurrence dates". As I`m not able to fix my X-axis to start from today`s date, I need a dummy item in my list with occurrence date = today(), hence I would like to import the spreadsheet and only have the occurrence date in the dummy item to be calculated to today(), the rest of the rows and columns should not be changed.

 

Any ideas?

Data Modeling

3 Comments

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi,

    I think you can use the “Filter rows” function in the Power query and select “Is before today”:

     

    https://docs.microsoft.com/en-us/power-query/filter-values

     

    If this can’t meet your requirement or you still have a problem, please refer to this link to get all the operations that can be done in the Power query:

    https://docs.microsoft.com/en-us/power-query/filter-values

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey,

     

    I managed to get this done by using Replaced Value as shown below.

     

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Occurrence date], each if [Risk ID]=0 then TodayDate else [Occurrence date] ,Replacer.ReplaceValue,{"Occurrence date"})


    in


    #"Replaced Value"

Completed Ideas