Forum Discussion
Date Banding
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"
[Count of Month] Is it a column or a measure?Do you want to create measure or column?
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:
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"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"
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.