Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago
Solved

Bandas de fechas

hola Estoy intentando escribir una instrucción IF anidada dentro del modelo de datos, pero me estoy atascando ya que necesito usar parámetros BETWEEN para agrupar mis datos p. ej.. Banda...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    Por favor refiérase a:

    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"