Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
STARTING END DATA
02/01/2020 19/12/2020 20
12/07/2020 19/12/2020 40
19/12/2020 19/12/2020 50
01/01/2020 07/07/2020 20
15/02/2019 01/07/2020 50
15/02/2019 01/10/2020 50
I would like to create a quarter formula according to the following example in power query
do you think this is possible?
Thanks
Solved! Go to Solution.
try this (last attempt)
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCcAwDAPAXfwOWDY1JbOY7JZZMlkdEqhLab/iJLkTlCGsUIxOhUaXyqJ3oKBWnCLC+cOOzZ7pi9li8ZhPY3mPp0NjzKLURSQR+yKCTNoF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, data = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"start", type date}, {"end", type date}, {"data", Int64.Type}}),
f={"Q1","Q2","Q3"},
ThirdOfYear=(ymd) => Number.IntegerDivide(Date.Month(ymd)+3,4),
third=(start)=> List.Transform({1..3},each if ThirdOfYear(start)>_ then "no" else 0 ),
ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(third(_[start]),f)&Record.FromList({_[data]},{f{ThirdOfYear(_[end])-1}}))),
ttrid=Table.AddIndexColumn(ttr,"id",1),
ft={"TR1","TR2","TR3","TR4"},
quarter=(start)=> List.Transform({1..4},each if Date.QuarterOfYear(start)>_ then "no" else 0 ),
ttrt=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(quarter(_[start]),ft)&Record.FromList({Text.From(_[data])},{ft{Date.QuarterOfYear(_[end])-1}}))),
ttrtid=Table.AddIndexColumn(ttrt,"id",1),
join= Table.NestedJoin(ttrtid, {"id"}, ttrid, {"id"}, "QandT", JoinKind.Inner)
in Table.ExpandTableColumn(join, "QandT", {"Q1", "Q2", "Q3"}, {"Q1", "Q2", "Q3"})
STARTING END DATA
02/01/2020 19/12/2020 20
12/07/2020 19/12/2020 40
19/12/2020 19/12/2020 50
01/01/2020 07/07/2020 20
15/02/2019 01/07/2020 50
15/02/2019 01/10/2020 50
Thanks,
Now i would like to do like this new example,
How do u think with these new columns ?
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTK01Dc00sfGMTVQitVBqDPSNzAnRh2x5pnqG4CkDC2BHANDJMNB6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, START = _t, REALIZATION = _t, data = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"year", Int64.Type}, {"START", type date}, {"REALIZATION", type date}, {"data", Int64.Type}}),
f={"TR1","TR2","TR3","TR4"},
quarter=(start)=> List.Transform({1..4},each if Date.QuarterOfYear(start)>_ then "no" else 0 ),
ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(quarter(_[START]),f)&Record.FromList({Text.From(_[data])},{f{Date.QuarterOfYear(_[REALIZATION])-1}})))
in
ttr
Hi @Anonymous
Thanks for your solution, do you if it is possible to add also on this syntax the concept of Q1 Q2 and Q3 like the new example?
Thanks
I can try to do that.
as i forgot about the problem a bit, you should upload an example of the source table and the desired table so that i can easily copy it.
STARTING ENDING DATA
02/01/2020 19/12/2020 20
12/07/2020 19/12/2020 40
19/12/2020 19/12/2020 50
01/01/2020 07/07/2020 20
15/02/2019 01/07/2020 50
15/02/2019 01/10/2020 50
Hi @Anonymous
I would like to achieve this result, thanks
In this case, the greatest difficulty is understanding what is required. I know little English, but in my working context the letter Q indicates the fourth of the year, that is a duration of 3 months. Here, if I interpret the clues you provide correctly, by Q you mean a duration of 4 months. In this sense I have modified the script.
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCcAwDAPAXfwOWDY1JbOY7JZZMlkdEqhLab/iJLkTlCGsUIxOhUaXyqJ3oKBWnCLC+cOOzZ7pi9li8ZhPY3mPp0NjzKLURSQR+yKCTNoF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, data = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"start", type date}, {"end", type date}, {"data", Int64.Type}}),
f={"Q1","Q2","Q3"},
third=(start)=> List.Transform({1..3},each if ThirdOfYear(start)>_ then "no" else 0 ),
ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(third(_[start]),f)&Record.FromList({_[data]},{f{ThirdOfYear(_[end])-1}})))
in
ttr
Hi @Anonymous
Thanks you for the script, but it does not look to work or i haven't understood
Where do you put the ThirdOfYear ?
I'm trying to have in the same table the Trimester = 3 months, like the first script and also the Qarter = 4 months
try this (last attempt)
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCcAwDAPAXfwOWDY1JbOY7JZZMlkdEqhLab/iJLkTlCGsUIxOhUaXyqJ3oKBWnCLC+cOOzZ7pi9li8ZhPY3mPp0NjzKLURSQR+yKCTNoF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, data = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"start", type date}, {"end", type date}, {"data", Int64.Type}}),
f={"Q1","Q2","Q3"},
ThirdOfYear=(ymd) => Number.IntegerDivide(Date.Month(ymd)+3,4),
third=(start)=> List.Transform({1..3},each if ThirdOfYear(start)>_ then "no" else 0 ),
ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(third(_[start]),f)&Record.FromList({_[data]},{f{ThirdOfYear(_[end])-1}}))),
ttrid=Table.AddIndexColumn(ttr,"id",1),
ft={"TR1","TR2","TR3","TR4"},
quarter=(start)=> List.Transform({1..4},each if Date.QuarterOfYear(start)>_ then "no" else 0 ),
ttrt=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(quarter(_[start]),ft)&Record.FromList({Text.From(_[data])},{ft{Date.QuarterOfYear(_[end])-1}}))),
ttrtid=Table.AddIndexColumn(ttrt,"id",1),
join= Table.NestedJoin(ttrtid, {"id"}, ttrid, {"id"}, "QandT", JoinKind.Inner)
in Table.ExpandTableColumn(join, "QandT", {"Q1", "Q2", "Q3"}, {"Q1", "Q2", "Q3"})
according to how you presenting what you want this its added columns to the existing table so will go like this:
Column 1 =>
if this solved your question give some kudos and mark as solution for others to find it easily, thanks.
Proud to be a Super User!
hey, I paste the dax formula of it if you want to add it to power query you will have to adapt the syntyx example:
Proud to be a Super User!