Add calculated row in power query

[ajinkyacgaikwad]

I have a data looks like following table.
I want to have a row that will give me entry for "Remaining Countries" for all the Dates  = "World" - (All the countries)

CountryDateValue

India	01-01-2020	5
China	01-01-2020	10
Bhutan	01-01-2020	1
India	01-01-2021	6
China	01-01-2021	12
Bhutan	01-01-2021	3
World	01-01-2020	50
World	01-01-2021	60

[Anonymous · Accepted solution]

Hi @ajinkyacgaikwad  ,

^{You can input the following code in “Advanced Editor”}

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lEyMNQFIiMDIwMgx1QpVidayTkjMw9DytAALOeUUVqSmIchCZbDMNIQyDHDbiRIytAIh5EgSWOwXHh+UU4KhisNsMuBrQPKxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Date = _t, Value = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Date", type date}, {"Value", Int64.Type}}),

    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Country]), "Country", "Value"),

    #"Inserted Multiplication" = Table.AddColumn(#"Pivoted Column", "Multiplication", each [World] * [India], Int64.Type),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Multiplication",{"Multiplication"}),

    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Remaining countries", each [World]-[India]-[China]-[Bhutan]),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Date"}, "Attribute", "Value")

in

    #"Unpivoted Other Columns"

^{The output is as follows}

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Xinru Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[ryan_mayu]

@ajinkyacgaikwad 

you can try to use DAX to create a new table

Table 2 = 
VAR tbl=ADDCOLUMNS( SUMMARIZE('Table','Table'[date],"value",sumx(FILTER('Table','Table'[Country]="World"),'Table'[value])-sumx(FILTER('Table','Table'[Country]<>"World"),'Table'[value])),"Country","Remaining Countries")
var tbl2=SELECTCOLUMNS(tbl,"Country",[Country],"date",'Table'[date],"value",[value])
return UNION('Table',tbl2)