Find all dates within first 7 days of month and change value in the query?

[Anonymous]

I have a table with 2 date columns. 

I want to replace values of both columns based on the following criteria: if date in column 1 is within the first 8 days of the month and if date in column 2 is within first 8 days of the month, set the value for both columns for the first day of that month

It will have to be an if statement written in M. 

Example: if #Effective Date & Status Change date = 

[Anonymous · Accepted solution]

HI @Anonymous,

You can refer to us following the M query formulas to achieve your requirement. (value replaced base on 'unpivot', 'pivot' columns and replace value functions)

let
    Source =
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "fVjLbls5DP0Vw+ssrp5XWg4w6CJAZxYtMIugizQ1pgVSp3DiAv77+koUdSjR2ZomxcfhIXkfHvZmf7f/9Ph2Pn17vNzt/nn5ffj59XDamXy3s4vZpB9fjkX29+Gpymwk2Ze7h729/uPD6Uf5x/35+bIzyya1i7D71/n/8+vbzpBoU3Rg+tPh1xvZDsX2ehX+d/h2PLxKtxJJNwP++p/PZ/rHh8PX0/nxdGkGvHz+1+nH885YEm3aQb7w8fHq+cpvd9VNYFd4NkLAFNbKEX/+fj69DiE5CLrYPh+ly6a6HEC2vero100vSW+78ZJRs4BqL6En2WYgg9eQbcsRt1Kwugi6vDCkizItAVQg4BYASPlDe7roVb+yCpCFZEXTiowy/nqZuPz3j8eaygrNVPWd0P/36e2F61Ge77Kmb6FWFUOUVfKvu85PD4VuvgfIKVuvGYsKNq0jUVFGjHWYrFprVPWAtUKQ3Z+Ph1bKZY7HAERMukEFrmv3OreQLJY6v+fbBBNs5qF7qmbUAgYIkaoZGvb09H3namQWqkAkEElQVK3OIRxw96iaNQkSZt3IfZSNqOLOLlBiK9iLXDMzsAsRLBhtGBK51ZdjhTwVCdWOopV+EQTI+qDNzRbQwCoMMELYQZT2bFroSZtuvEPBy5YdR46rNpDMGIgrh8HYb6IMMTgNaOEGWCymzxksTHctUHiKTwEYwaH5UhyvJZ6qIn12SlQd4J1cN4hKn71Kc+qQJYQjlbgwD7uosAFDHLrDxQGPzW/fp93QtCwq+uvcIdawbqf+LZWEQNJMaldT58ehrS8NXLUxHYKrF9l13XHmOAMZ88t+3B6IM4aBs3kdAR7eaKsB9Y4IeLMZIFxvlUTBkOuJem7URk8irDhUN60E7E1DelH20icg3aB0mMVtxst/SPR4bRmhvaCubj5qqapLmJl6uG59tk4ov85eJ15j5mbATdcnvVM7cfW6w4qVgLl8Vhp5vc0fuM2ERQNf3ygm5x3OmyC3sE2e+d2WsIJWj29aEVVvpcTkMyRTDJwg2bztHH28vr+zhmGJxxUM3YZteEHnBXlVLGRuC8HEYu0MUYifeYCgWis+tnCQjEX/CMpxIJuN9s0w4Ks+vWgFbqPbYIWzUg3Ljg9DAnkgCmT1AyAysOcT0AGqI2ILipGUyFuv4yUYBY7qBcFF1i4q46DM0e3lfUF7shurSJONfNYHo2s8M20CBggo4qnWuTpyU/CWXXVxlsehlH0B8rdHjNh3I3Iz57NvIROjW5Gum0ldFfIepkKl75jHjPfpOGymBhG63mgCs/DL08ih3JEBZHfOTR9Y4z6NB8Y6gKytzcOHCriX2snuNL+M8iq1Fq4D6wCzYt3xwGml6lXwMHBWMfZ5SpoJor3SGVC6xhulXJTzpmZsxdcRZluV0xxxjQfPk1XsXvxm4gpPq5VJAK01vwcRu6jGa9RUrqTPlZWJcO5bJOFk1Jwv7/UGd04xgP0/6CNTdZrNQEtJTkyxVskzl6gFCpbwe0VlvMxZm4eHEVmTK3VrLdM7hD9M9fHRdHFeMh85RqmSMQ8wTeoHDV4Epts/wIaQkO/awE1MRvNMQjZJ+jcxkxXPpw+U1fe8aIQUlSNFXqkVKtkoieubFcTNn67a0VfU7YCVi3qblQsHD7PstDb073SoQaflUiZZfv4AwV9Eim5Q0E35nr7HFpzhnZPFaCT8w00nSQkHT5YfnxpQ7ITuXuYIAM9iN98aq20SQ7+1xaMoSSbjvoiMromk8PO0WeS6v5nXqitBf/X3yx8=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ((type nullable text)
                meta
                [
                    Serialized.Text = true
                ])
            in
                type table [
                    ID = _t,
                    #"Effective Date" = _t,
                    #"Status Change date" = _t
                ]
        ),
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {
                    "ID",
                    Int64.Type
                },
                {
                    "Effective Date",
                    type date
                },
                {
                    "Status Change date",
                    type date
                }
            }
        ),
    #"Unpivoted Columns" =
        Table.UnpivotOtherColumns(
            #"Changed Type",
            {"ID"},
            "Attribute",
            "Value"
        ),
    #"Replaced Value" =
        Table.ReplaceValue(
            #"Unpivoted Columns",
            each [Value],
            each
                if Date.Day([Value]) <= 8 then
                    #date(
                        Date.Year([Value]),
                        Date.Month([Value]),
                        1
                    )
                else
                    [Value],
            Replacer.ReplaceValue,
            {"Value"}
        ),
    #"Pivoted Column" =
        Table.Pivot(
            #"Replaced Value",
            List.Distinct(#"Replaced Value"[Attribute]),
            "Attribute",
            "Value"
        )
in
    #"Pivoted Column"

Raw table:

Replaced:

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , create two new columns like

new effective date = if([effective date] >=eomonth([effective date],-1)+1 && [effective date] <=eomonth([effective date],-1)+8 &&
[Status change date] >=eomonth([Status change date],-1)+1 && [Status change date] <=eomonth([Status change date],-1)+8, eomonth([effective date],-1)+1,[effective date])

Status change date = if([effective date] >=eomonth([effective date],-1)+1 && [effective date] <=eomonth([effective date],-1)+8 &&
[Status change date] >=eomonth([Status change date],-1)+1 && [Status change date] <=eomonth([Status change date],-1)+8, eomonth([Status change date],-1)+1,[Status change date])

[Anonymous]

---

@amitchandak wrote:

@Anonymous, create two new columns like

new effective date = if([effective date] >=eomonth([effective date],-1)+1 && [effective date] <=eomonth([effective date],-1)+8 &&
[Status change date] >=eomonth([Status change date],-1)+1 && [Status change date] <=eomonth([Status change date],-1)+8, eomonth([effective date],-1)+1,[effective date])

Status change date = if([effective date] >=eomonth([effective date],-1)+1 && [effective date] <=eomonth([effective date],-1)+8 &&
[Status change date] >=eomonth([Status change date],-1)+1 && [Status change date] <=eomonth([Status change date],-1)+8, eomonth([Status change date],-1)+1,[Status change date])

---

I get an error Token RightParen expected at (where the comma is red): <=eomonth([Status change date],-1)+8, eomonth([effective date],-1)+1,[effective date])