Change a dimension according to date

[Jo_Chrq]

Hi all,

I'm facing an issue here :

In my store table, I have a field that classifies stores in subsidiary or franchise.

One store will change from Fra to Sub in April and I would like it to appear in the Fra report before April and in Sub after April.

Is there a way to do so?

Many thanks in advance

Jo

[Anonymous · Accepted solution]

Hi @Jo_Chrq ,

I think you can add this code in advanced editor to replace "Subsidiary" to "Franchise" if [Store] = xxx and [Date] >= 2022/04/01.

...,
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Subsidiary",each if [Store] = "A" and [Date]>=#date(2022, 4, 1) then "Franchise" else [Category],Replacer.ReplaceText,{"Category"})
in
    #"Replaced Value"

Sample:

Result is as below.

The whole M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLSNzDWNzAEcoJLk4ozUzITiyqVYnWQ5U2wyjvB5Q0h8m5FiXnJGZnFqWBpZwLGu6Abj6Q9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Date = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Date", type date}, {"Category", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Subsidiary",each if [Store] = "A" and [Date]>=#date(2022, 4, 1) then "Franchise" else [Category],Replacer.ReplaceText,{"Category"})
in
    #"Replaced Value"

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.