Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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"
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"
Thanks @Anonymous for your assistance. Yes, that I was exactly looking for. Thanks again.
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
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
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello @ashisha11
in your AddQ2 step you have to refer to your previous step AddQ1 and not to TransDate
BR
Jimmy
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:
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.
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
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.
Hello @ashisha11
this is because i used only one row in my database to show you how it works 🙂
BR
Jimmy
Is it that difficult? Is it possible in Power BI?