Applied Steps not applying to the whole query

[symun23]

Hello,

I have modelled a table and it is working fine. I now have more data that I need to add to the table.

One of the steps is to replace a value and then change it to a date. As I said this works fine currently but when I add extra data and more values to replace and change it is not applying the following steps to the new data. So when I click on 'Replaced Value' step and 'Changed Type' step the data has change but when I then click on the next step the changes have disappeared.

Anyone know why this might be happening and how to get around it with out starting the whole applied steps again?

[jthomson]

When you say you're adding extra data, what do you mean? You're appending/merging another similar query into the one you're working on?

[symun23]

Hi jthomson,

I mean I am added more rows in to the data, here is the code as it is,

#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","201821","11.06.2018",Replacer.ReplaceText,{"Print_DT_Initial_Letter"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","201822","18.06.2018",Replacer.ReplaceText,{"Print_DT_Initial_Letter"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","201823","25.06.2018",Replacer.ReplaceText,{"Print_DT_Initial_Letter"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","201824","02.07.2018",Replacer.ReplaceText,{"Print_DT_Initial_Letter"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value6",{{"Print_DT_Initial_Letter", type date}}),

The column 'Print_DT_Initial_Letter' is type text. I am replacing the values and then changing it to type date. I am trying to add in extra steps so essentially "Replaced Value7" would be "201825", "09.07.2018". When I add this step in though it is not updating any steps for the rest of the query.

Hope this adds some clarity?

[jthomson · Accepted solution]

I would have thought it would be much, much easier to just create a lookup table between your year/week numbers and the required dates, rather than adding in an additional step each time which seems incredibly clunky, but if you added in your Replaced Value7 line after the Replaced Value6 line and before the Changed Type2, altering the Value6 to Value7 in the changing type stage, then there's no reason why it shouldn't work

[symun23]

Yea my bad, forgot to change the value 6 to 7 in the Changed Type. Cheers jthomson

Not sure how to work it with the lookup table, so this was the only way I was able to do it.