Forum Discussion

Jo_Chrq's avatar
Jo_Chrq
Icon for Helper I rankHelper I
4 years ago
Solved

Change a dimension according to date

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

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.