Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Thanks, I have tried the following but the results only show 0-3mths
@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
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
[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.
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |