Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Expandir fechas en Power Query/M

hola Tengo una tabla con fechas desconectadas a continuación, me gustaría tener una tabla con fechas continuas con la misma información entre las fechas. Simplemente expanda las fechas hasta un d...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    Hay @marc_hll,

    Pruebe la siguiente sintaxis M:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcy5DQAhDETRXhwj4TGHKYMc0X8by2IHyMEkT6O/Fk1QIj3rGS0LC2gn52E8AoOvizwuh2qzfw2u3tHg8H9/vPxerM/u+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Units = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Units", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let Category_ = [Category],Date_ = [Date] in List.Min(Table.SelectRows(#"Changed Type", each [Category] = Category_ and [Date] > Date_)[Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDate", each if [Custom] is null then Date.AddDays(DateTime.Date(DateTime.LocalNow()) ,-1) else Date.AddDays([Custom],-1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each { Number.From([Date])..Number.From([EndDate]) }),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.2",{{"Custom.2", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date", "Custom", "EndDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Date"}})
in
    #"Renamed Columns"

    La salida final se muestra a continuación:

    fill date based on category.PNG

    Saludos
    Eyelyn Qin
    Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

hola

¿Cuál es la base de dividir la fila 1 en 3 filas? fila 2 en 4 filas? Dé una explicación detallada.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    @Ashish_Mathur , he editado mi post con más detalles. La base es expandir las fechas hasta un día antes de la fecha en la fila siguiente con la misma información entre las fechas.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      hola

      Este código M funciona

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTI01TUw0zUyMDJUitWJVrIACVmgCBkaAMWMjBBisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Units = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [Date] { [Index]  + 1 } otherwise [Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Date]=[Custom] then [Custom] else Date.AddDays([Custom],-1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date.1", each {Number.From([Date])..Number.From([Custom.1])}),
    #"Expanded Date.1" = Table.ExpandListColumn(#"Added Custom2", "Date.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date.1",{"Date", "Index", "Custom", "Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date.1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date.1", "Date"}})
in
    #"Renamed Columns"

      Espero que esto ayude.

      Untitled.png