Need help in creating Column for showing quarterly information.

[ashisha11]

 

Hello Folks,

I am stuck in one of my client's requirement. Need help to create columns that are shown in blue color basis on the two dates column shown in the report. Please help me.

Unique ID	Dock Date	Live Date	Q1	Q2	Q3	Q4
101	2/14/2021	4/14/2021	Dock	Live
102	7/6/2021	3/25/2021	Live		Dock
103	5/14/2021	8/14/2021		Dock	Live
104	6/11/2021		Dock
105		9/1/2021				Live

[Anonymous · Accepted solution]

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRMtI3NNE3MjACsU2Q2C75ydlAyiezLBVIKYBxrA5IlxGQba5vBlNorG9kCmMjqYbqh2kyBrJNkYw3RLZXAYt9EG0mIKUG+oaGWJQqoLnLFMq31EdRDcNgk2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Dock Date" = _t, #"Live Date" = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),

       Q=(d)=> if d=null then "" else Text.From(Number.IntegerDivide(Date.Month(d)-1,3)+1 ),
       rec=[Q1="",Q2="",Q3="",Q4=""],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Dock Date", type date}, {"Live Date", type date}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}},"en-US"),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Unique ID", "Dock Date", "Live Date"}),

    #"Added Custom2" = Table.AddColumn(#"Removed Other Columns", "Q", each rec & Expression.Evaluate("[Q" & Q([Dock Date]) & " =""dock""]")&Expression.Evaluate("[Q" & Q([Live Date]) & " =""live""]")),
    #"Expanded dock" = Table.ExpandRecordColumn(#"Added Custom2", "Q", {"Q1", "Q2", "Q3", "Q4"}, {"Q1", "Q2", "Q3", "Q4"})
in
    #"Expanded dock"

[ashisha11]

Thanks @Anonymous for your assistance. Yes, that I was exactly looking for. Thanks again.

[ziying35]

Hi, @ashisha11 

The Dock Date and Live Date fields don't have a span date and the same quarter date in a row of data, do they?

If the above scenario doesn't exist, I guess could write a solution

[Jimmy801]

Hello @ashisha11 

try this code. This shows how to do with Q1. Add 3 other steps for Q2 - Q3.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRMzDSMzIwMlDSUTI01TMwhXBiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dock Date" = _t, #"Live Date" = _t]),
    TransDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Dock Date",
                each Date.From(_, "de-DE")
            },
                        {
                "Live Date",
                each Date.From(_, "de-DE")
            }
        }
    ),
    AddQ1 = Table.AddColumn
    (
        TransDate,
        "Q1",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 1 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 1 then "Live" else ""
    )
in
    AddQ1

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ashisha11]

Thanks @Jimmy801 for looking into this, I am trying to create columns for other Quarters but at a time I am able to see only one- I am using below code - Could you please see and advise-

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRMzDSMzIwMlDSUTI01TMwhXBiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dock Date" = _t, #"Live Date" = _t]),
TransDate = Table.TransformColumns
(
Source,
{
{
"Dock Date",
each Date.From(_, "de-DE")
},
{
"Live Date",
each Date.From(_, "de-DE")
}
}
),

AddQ1 = Table.AddColumn
(
TransDate,
"Q1",
(add)=> if Date.QuarterOfYear(add[Dock Date])= 1 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 1 then "Live" else ""
),

AddQ2 = Table.AddColumn
(
TransDate,
"Q2",
(add)=> if Date.QuarterOfYear(add[Dock Date])= 2 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 2 then "Live" else ""
)


in
AddQ2

[mahoneypat]

Your AddQ2 step needs to have AddQ1 and the previous step, not TransDate to get @Jimmy801 solution to work.

AddQ2 = Table.AddColumn
(
AddQ1,  //not TransDate here
"Q2",

Regards,

Pat

[Jimmy801]

Hello @ashisha11 

in your AddQ2 step you have to refer to your previous step AddQ1 and not to TransDate

BR

Jimmy

[ashisha11]

Hi @mahoneypat , Thank you for your valuable input. I tried below code for all quarters at a time I am getting only one row for dates. Please see below image. I have more rows. Will it work for only one row at a time? Also I have null rows as well in my data. How it will take care of that?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRMzDSMzIwMlDSUTI01TMwhXBiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dock Date" = _t, #"Live Date" = _t]),
    TransDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Dock Date",
                each Date.From(_, "de-DE")
            },
                        {
                "Live Date",
                each Date.From(_, "de-DE")
            }
        }
    ),
    
AddQ1 = Table.AddColumn
    (
        TransDate,
        "Q1",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 1 then "Dock" 
                else if Date.QuarterOfYear(add[Live Date])= 1 then "Live" 
                else ""      
    ),

AddQ2 = Table.AddColumn
(
AddQ1,  //not TransDate here
"Q2",
 (add)=> if Date.QuarterOfYear(add[Dock Date])= 2 then "Dock" 
                else if Date.QuarterOfYear(add[Live Date])= 2 then "Live" 
                else ""
       
    ),

AddQ3 = Table.AddColumn
(
AddQ2,  //not TransDate here
"Q3",
 (add)=> if Date.QuarterOfYear(add[Dock Date])= 3 then "Dock" 
                else if Date.QuarterOfYear(add[Live Date])= 3 then "Live" 
                else ""
       
  ),

AddQ4 = Table.AddColumn
(
AddQ3,  //not TransDate here
"Q4",
 (add)=> if Date.QuarterOfYear(add[Dock Date])= 4 then "Dock" 
                else if Date.QuarterOfYear(add[Live Date])= 4 then "Live" 
                else ""
       
  )
in
    AddQ3
    

Output:

[ashisha11]

Hi @Jimmy801 ,

May be you putting it right but I am still not able to create for other quarters. Not sure What I am missing to add. Could you please help me with adding of Q2 as well. It will be a great help. 

Thanks in advance.

[Jimmy801]

Hello @ashisha11 

here the complete code for 4 columns for easy understanding

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRMzDSMzIwMlDSUTI01TMwhXBiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dock Date" = _t, #"Live Date" = _t]),
    TransDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Dock Date",
                each Date.From(_, "de-DE")
            },
                        {
                "Live Date",
                each Date.From(_, "de-DE")
            }
        }
    ),
    AddQ1 = Table.AddColumn
    (
        TransDate,
        "Q1",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 1 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 1 then "Live" else ""
    ),
    AddQ2 = Table.AddColumn
    (
        AddQ1,
        "Q2",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 2 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 2 then "Live" else ""
    ),
    AddQ3 = Table.AddColumn
    (
        AddQ2,
        "Q3",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 3 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 3 then "Live" else ""
    ),
    AddQ4 = Table.AddColumn
    (
        AddQ3,
        "Q4",
        (add)=> if Date.QuarterOfYear(add[Dock Date])= 4 then "Dock" else if Date.QuarterOfYear(add[Live Date])= 4 then "Live" else ""
    )
in
    AddQ4

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ashisha11]

Hi @Jimmy801 , I tried your suggested way however getting only one row at a time.

The desired output is below which I got from @Anonymous 's post. Thanks a ton to both of you.

[Jimmy801]

Hello @ashisha11 

this is because i used only one row in my database to show you how it works 🙂

BR

Jimmy

[ashisha11]

Is it that difficult? Is it possible in Power BI?

[ashisha11]

Hi @amitchandak , would you mind look into this please. Need your help sir.