Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AuroraNI
Helper III
Helper III

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

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

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

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

HI @FrankAT gracias por la ayuda.

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

FrankAT
Community Champion
Community Champion

Hola @AuroraNI ,

Sí, ¡úsalo en Power Query!

Saludos FrankAT

amitchandak
Super User
Super User

@AuroraNI ,

Trate como

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak obtengo el siguiente error:

Capture.PNG

Gracias por su ayuda con este

@AuroraNI, creo que olvidé -1

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Capture.PNG

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hola @amitchandak sigues sin funcionar

Capture.PNG

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors