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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jmuthukumaran
Frequent Visitor

insert row in the table

Team , 

I need to add a new row in a table using sum formula , is it possible to achieve in power bi?

jmuthukumaran_0-1649170115875.png

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Solution uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuUOWen72PIQ9RqBY?e=y6yIoA 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Number.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Country"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Category" and [Attribute] <> "Index")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country", "Attribute"}, {{"Temp", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Temp"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Category", each "Cuts"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Index", each List.Max(Table.SelectRows(#"Added Index", (x)=> x[Country]=[Country])[Index])+0.1),
    #"Appended Query" = Table.Combine({#"Added Index", #"Added Custom1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Solution uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuUOWen72PIQ9RqBY?e=y6yIoA 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Number.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Country"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Category" and [Attribute] <> "Index")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country", "Attribute"}, {{"Temp", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Temp"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Category", each "Cuts"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Index", each List.Max(Table.SelectRows(#"Added Index", (x)=> x[Country]=[Country])[Index])+0.1),
    #"Appended Query" = Table.Combine({#"Added Index", #"Added Custom1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

jmuthukumaran
Frequent Visitor

Thanks for the response, 

My Source is an excel file where it contains country , category(order, ship only), Months (Jan , Feb , so on ) as columns , i need to create a tabular report like for each country data  need to add a new row at the end called "cuts" with formula (sum of order - sum of ship) for that country. hope this helps

serpiva64
Solution Sage
Solution Sage

Hi,

it's not clear to me what you need, can you explain again and make an exemple?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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