March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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.
Solved! Go to Solution.
Hola @AuroraNI ,
// 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
Hola @AuroraNI ,
// 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?
Trate como
diff - [Muerte] - maxx (Tabla(Tabla,Tabla[Courty/Region]- antes (Tabla[Courty/Region]) - anterior (Tabla[fecha]),[Muerte])
@AuroraNI, creo que olvidé -1
diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])-1),[Death])
@amitchandakSigue recibiendo el mismo error? ¿Tiene algo que ver con cómo se trata de la primera fila?
-1 está fuera)
diff - [Muerte] - maxx (Tabla (Tabla,Tabla[Courty/Region] - anterior (Tabla[Courty/Region]) - anterior (Tabla[fecha]- 1),[Muerte])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |