Reply
AuroraNI
Helper III
Helper III
Syndicated - Inbound

Crear columna que resta el valor anterior del siguiente valor en función de otros criterios de columna

Source Community: Power BI

Hola a todos

Puede alguien ayudar con el código Descelóse para crear una nueva columna en el editor de consultas. Me gustaría restar las muertes del día anterior de los próximos días para cada país específico, estoy luchando con averiguar para hacer esto cuando cambia de país.

Capture.PNG

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Source Community: Power BI
Syndicated - Inbound

Hola @AuroraNI ,

  1. Columna de grupo País/Región.
  2. Echa un vistazo al código mashup:
// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFNCsMgEAXgqwTXgZhR87MM7QlaupIsLJU0UBRsuujtK0jDZAxZiX4wT+ZpzU7+45bwrS52mr0rzmaxrGT/w5rl+S7YWGo2vO7GzSa+QldxUQEHHi81p9ofqeBYa6o1VkWUR5WZTjak3N3Jq+5OXhWwtlTFoUqsPVWFFLJfNVizX7VYG6q4BchycQsi5d6uA21AKCx4R1Ii2WxedSnLPXwIeWtAdNtanDr+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Country/Region Date", "Country/Region"}, {"Deaths ", "Deaths"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Deaths", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country/Region"}, {{"Country", each _, type table [#"Country/Region"=text, Date=date, #"Deaths"=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Running Total", each fxFunction([Country])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Running Total"}),
    #"Expanded Running Total" = Table.ExpandTableColumn(#"Removed Other Columns", "Running Total", {"Country/Region", "Date", "Deaths", "Saldo"}, {"Country/Region", "Date", "Deaths", "Saldo"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Running Total",{{"Country/Region", type text}, {"Date", type date}, {"Deaths", Int64.Type}, {"Saldo", Int64.Type}})
in
    #"Changed Type1"

// fxFunction
(tabelle as table) =>
let
    #"Sorted Rows" = Table.Sort(tabelle,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Saldo", each List.Sum(List.Range(#"Added Index"[Deaths],0,[Index]))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Saldo", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Index"})
in
    #"Removed Columns"

Saludos FrankAT

View solution in original post

9 REPLIES 9
FrankAT
Community Champion
Community Champion

Source Community: Power BI
Syndicated - Inbound

Hola @AuroraNI ,

  1. Columna de grupo País/Región.
  2. Echa un vistazo al código mashup:
// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFNCsMgEAXgqwTXgZhR87MM7QlaupIsLJU0UBRsuujtK0jDZAxZiX4wT+ZpzU7+45bwrS52mr0rzmaxrGT/w5rl+S7YWGo2vO7GzSa+QldxUQEHHi81p9ofqeBYa6o1VkWUR5WZTjak3N3Jq+5OXhWwtlTFoUqsPVWFFLJfNVizX7VYG6q4BchycQsi5d6uA21AKCx4R1Ii2WxedSnLPXwIeWtAdNtanDr+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Country/Region Date", "Country/Region"}, {"Deaths ", "Deaths"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Deaths", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country/Region"}, {{"Country", each _, type table [#"Country/Region"=text, Date=date, #"Deaths"=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Running Total", each fxFunction([Country])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Running Total"}),
    #"Expanded Running Total" = Table.ExpandTableColumn(#"Removed Other Columns", "Running Total", {"Country/Region", "Date", "Deaths", "Saldo"}, {"Country/Region", "Date", "Deaths", "Saldo"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Running Total",{{"Country/Region", type text}, {"Date", type date}, {"Deaths", Int64.Type}, {"Saldo", Int64.Type}})
in
    #"Changed Type1"

// fxFunction
(tabelle as table) =>
let
    #"Sorted Rows" = Table.Sort(tabelle,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Saldo", each List.Sum(List.Range(#"Added Index"[Deaths],0,[Index]))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Saldo", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Index"})
in
    #"Removed Columns"

Saludos FrankAT

Source Community: Power BI
Syndicated - Inbound

HI @FrankAT gracias por la ayuda.

¿Cómo usarías este código? ¿En el editor de consultas?

FrankAT
Community Champion
Community Champion

Source Community: Power BI
Syndicated - Inbound

Hola @AuroraNI ,

Sí, ¡úsalo en Power Query!

Saludos FrankAT

amitchandak
Super User
Super User

Source Community: Power BI
Syndicated - Inbound

@AuroraNI ,

Trate como

diff - [Muerte] - maxx (Tabla(Tabla,Tabla[Courty/Region]- antes (Tabla[Courty/Region]) - anterior (Tabla[fecha]),[Muerte])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Source Community: Power BI
Syndicated - Inbound

@amitchandak obtengo el siguiente error:

Capture.PNG

Gracias por su ayuda con este

Source Community: Power BI
Syndicated - Inbound

@AuroraNI, creo que olvidé -1

diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])-1),[Death])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Source Community: Power BI
Syndicated - Inbound

@amitchandakSigue recibiendo el mismo error? ¿Tiene algo que ver con cómo se trata de la primera fila?

Capture.PNG

Source Community: Power BI
Syndicated - Inbound

-1 está fuera)
diff - [Muerte] - maxx (Tabla (Tabla,Tabla[Courty/Region] - anterior (Tabla[Courty/Region]) - anterior (Tabla[fecha]- 1),[Muerte])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Source Community: Power BI
Syndicated - Inbound

Hola @amitchandak sigues sin funcionar

Capture.PNG

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)