Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Juju-PowerBI
Frequent Visitor

Power Query date Interval IF

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

 

data time.PNG

 

I would like to create a quarter formula according to the following example in power query

do you think this is possible?

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"})

 

View solution in original post

11 REPLIES 11
Juju-PowerBI
Frequent Visitor

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

 

data time.PNG

 

Thanks,

Now i would like to do like this new example,

How do u think with these new columns ?

Anonymous
Not applicable

 

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

Anonymous
Not applicable

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

 

data time.PNG

 

Hi @Anonymous 

 

I would like to achieve this result, thanks

Anonymous
Not applicable

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

 

image.png

 

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

 

 

PART1.PNGPART2.PNGTHIRD.PNG

Anonymous
Not applicable

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"})

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

according to how you presenting what you want this its added columns to the existing table so will go like this: 

Column 1 => 

TR1 = IF(
QUARTER('Table'[START])=1,
IF( QUARTER('Table'[REALIZATION])=1,CONVERT('Table'[data],STRING),
"0"),"no")
column 2 => 
TR2 = IF(
QUARTER('Table'[START])<=2,
IF( QUARTER('Table'[REALIZATION])=2,CONVERT('Table'[data],STRING),
"0"),"no")
column 3 => 
TR3 = IF(
QUARTER('Table'[START])<=3,
IF( QUARTER('Table'[REALIZATION])=3,CONVERT('Table'[data],STRING),
"0"),"no")
column 4 =>
TR4 = IF(
QUARTER('Table'[START])<=4,
IF( QUARTER('Table'[REALIZATION])=4,CONVERT('Table'[data],STRING),
"0"),"no")

 

 

StefanoGrimaldi_0-1611258863807.png

 

if this solved your question give some kudos and mark as solution for others to find it easily, thanks. 

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Bonjour @StefanoGrimaldi 

 

Pouriez vous montrer la formule ? en essayant cela na pas fonctionné

 

 

Capture.PNG

Merci

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: 

TR1 =
if Date.QuarterOfYear(start date) = 1  then IF Date.QuarterOfYear(realization date)=1 then [data] else 
"0" else "no"
something like that, before this step you need to make the data column a text type for the M code to work here. them after this step you can make back the data column to value.

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors