Date Banding

[Andy1927]

Hi

I'm trying to write a Nested IF statement within the Data Model but I'm getting stuck as I need  to use BETWEEN parameters to group my data

e.g

Banding = IF([Count of Month] <0, "Expired", IF([Count of Month] <3, "0-3mths"))

How to I change the query to show that if the Count of Month is between 0 and 3 then "0-3mths".

I will need to continue this for 3-6, 6-12 mths and so on

Thanks

Andy

[Andy1927]

Thanks, I have tried the following but the results only show 0-3mths

Banding = SWITCH(TRUE(),[Count of Month] <0, "Expired", [Count of Month] <=3, "0-3mths", [Count of Month] <=6, "3-6mths", [Count of Month] <=12, "6-12mths", "0thers")

[amitchandak]

@Andy1927 , Create a measure like

Switch( True() ,

[Count of Month] <=3 , "0-3mths" ,

[Count of Month] <=6 , "3-6mths" ,

[Count of Month] <=12 , "6-12mths" ,

"Others"

)

But you might need dynamic segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Andy1927]

Sorry but this still does not provide me a solution.

Is there a simple way that I can band (group) dates from Todays date showing anything before today as "Expired"  and then 0-3 mths, 3-6mths and so on?

Thanks

Andy

[V-lianl-msft]

[Count of Month] Is it a column or a measure?Do you want to create measure or column?

[Andy1927]

It would be a column. I have found a solution using DAX in the Data Model but I would like to learn the equivalent in Power Query:

Date Banding = VAR A = DATEDIFF ( TODAY (), [Actual End Date], MONTH ) RETURN IF ( ISBLANK ( [Actual End Date] ), BLANK (), SWITCH ( TRUE (), A <= 0, "Expired", A <= 2, "0-3mths", A >= 3 && A <= 5, "3-6mths", A >= 6 && A <= 11, "6-12mths", A <= 17, "12-18mths", A <= 23, "18-24mths", A >= 24, "24mths+" ) )

Can this be replicated in Power Query?

Thanks,

Andy

[V-lianl-msft · Accepted solution]

Please refer to:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BDcAwCAPAXfKupZoobZglyv5rlPRjP0/YwFot7uggord9/QoMPMJECpUTKuidKbzeSZ8kaGLoEms5hUAXhsfsIZ599cT+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Date(DateTime.FixedLocalNow() )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each ((Date.Year([Actual End Date])-Date.Year([Custom]))*12) + Date.Month([Actual End Date]) - Date.Month([Custom])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= 0 then "Expired" else if [Custom.1] <= 2 then "0-3mths" else if [Custom.1] <= 5 then "3-6mths" else if [Custom.1] <= 11 then "6-12mths" else if [Custom.1] <= 17 then "12-18mths" else if [Custom.1] <= 23 then "18-24mths" else "24mths+"),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Actual End Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom", "Custom.1"})
in
    #"Removed Columns"

[Andy1927]

Thanks for this, here is the existing code with # symbols replacing the actual address of the sharepoint lists - I've done this for security reasons

How do I include or where do I copy & paste your code into the existing one below?

let

    Source = SharePoint.Files("https://#######.sharepoint.com/sites/####/205", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),

    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Folder Path], "PBI")),

    #"Activity Log for PowerBI xlsx_https://####### sharepoint com/sites/####/205/Utilities Data and Reports (12 21 01 13)/PBI/" = #"Filtered Rows1"{[Name="Activity Log for PowerBI.xlsx",#"Folder Path"="https://#######.sharepoint.com/sites/####/205/Utilities Data and Reports (12.21.01.13)/PBI/"]}[Content],

    #"Imported Excel" = Excel.Workbook(#"Activity Log for PowerBI xlsx_https://####### sharepoint com/sites/####/205/Utilities Data and Reports (12 21 01 13)/PBI/"),

    ActivityLog0403_Sheet = #"Imported Excel"{[Item="ActivityLog0403",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(ActivityLog0403_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Core Group", type text}, {"Directorate", type text}, {"SRO", type text}, {"Project Type", type text}, {"Nature of Procurement (only to be completed for procurement's)", type text}, {"Key contact", type text}, {"Title of Project", type text}, {"Description of the Project", type text}, {"Business Case Required", type text}, {"Stage of the Business Case Cycle", type text}, {"Date of next Business Case Stage", type date}, {"Is this in scope of CAB?", type text}, {"Is this in scope of PIC?", type text}, {"Estimated Project Value", type number}, {"Spend Category", type text}, {"Spend Category (if other)", type text}, {"Proposed Project Start Date, i.e. the launch of any competitive process or negotiation etc.", type date}, {"Who is providing commercial support?", type text}, {"Original Contract Reference No. OR Contract Title as per the contract register - For existing contracts only", type text}, {"Existing Contract End Date - For existing contracts only", type date}, {"Existing Supplier - For existing contracts only", type text}, {"Level of Competition", type text}, {"Proposed Sourcing Route", type text}, {"Sourcing Route (If Other)", type text}, {"Planned Project Start Date (e.g. the commencement date for the contract or grant)", type date}, {"Planned Project End Date (e.g. the expiry date for a grant/contract/project)", type date}, {"Tender Reference Number", type text}, {"Indicative Contract Tiering (only for contracts)", type text}, {"Comments", type text}, {"Can this Record be Published", type logical}, {"Reasons for non-publication", type text}, {"Commercial Lead", type text}, {"Current activity stage of project", type text}, {"Item Type", type text}, {"Path", type text}}),

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Path"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Proposed Project Start Date, i.e. the launch of any competitive process or negotiation etc.", "PP Start Date"}, {"Date of next Business Case Stage", "Next Business Case Stage"}, {"Estimated Project Value", "Est Project Value"}, {"Existing Contract End Date - For existing contracts only", "EC End Date"}, {"Planned Project Start Date (e.g. the commencement date for the contract or grant)", "Planned Project Start Date"}, {"Planned Project End Date (e.g. the expiry date for a grant/contract/project)", "Planned Project End Date"}, {"Can this Record be Published", "Published Y / N"}, {"ID", "Id."}}),

    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","TRN ","",Replacer.ReplaceText,{"Original Contract Reference No. OR Contract Title as per the contract register - For existing contracts only"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","TRN: ","",Replacer.ReplaceText,{"Original Contract Reference No. OR Contract Title as per the contract register - For existing contracts only"}),

    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," and "," & ",Replacer.ReplaceText,{"Core Group"}),

    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," and "," & ",Replacer.ReplaceText,{"Directorate"}),

    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,"Unknown",Replacer.ReplaceValue,{"Current activity stage of project"}),

    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value4", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    #"Filtered Rows2" = Table.SelectRows(#"Removed Blank Rows", each ([#"Id."] <> null)),

    #"Replaced Value5" = Table.ReplaceValue(#"Filtered Rows2",null,0,Replacer.ReplaceValue,{"Est Project Value"}),

    #"Filtered Rows3" = Table.SelectRows(#"Replaced Value5", each ([Directorate] <> null))

in

    #"Filtered Rows3"

[V-lianl-msft]

You can view the sample file I shared. There are detailed steps. First, add a custom column to calculate the date difference, then create a conditional column to group based on the date difference column.

[Andy1927]

I've tried to open your file but am unable to do so, my organisation is still using an older version of Desktop. Is there any other way I could view it?

[V-lianl-msft]

Hi @Andy1927 ,

If you can't open the pbix file, please create a new blank query and paste the code I provided before.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.