Change the format of a table

Here's one way to create a Totals Row from your data in M Code

let

//change next line to reflect data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Car Model", type text}} & List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, Int64.Type})),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Car Model"}, "Attribute", "Value"),

//Group by Attribute and SUM
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Yearly Total", each List.Sum([Value]), type number}}),

//Pivot, then combine with original table
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Yearly Total", List.Sum),
    #"Add Totals Row" = Table.Combine({#"Changed Type", #"Pivoted Column"}),
    #"Replaced Value" = Table.ReplaceValue(#"Add Totals Row",null,"Grand Total",Replacer.ReplaceValue,{"Car Model"})
in
    #"Replaced Value"

Data

Results

In Power BI, you can take your original data source and use the Matrix visualization -- just designate the Car Model as Rows and each of the Value columns as Values

Hi,

Your current data is in a pivot format, where each year (e.g., 2021, 2022) is a separate column. To work effectively in Power BI and ensure scalability for future years without the need for manual adjustments, it’s best to transform this into a tabular format. So your idea to unpivot everything is correct. You basically have to make sure that the years (2021,2021,...) do not show up in your code, you want something like this:

Model Year Sales

Tesla X 2020 12000

Tesla Y 2020 14000

After you unpivot the table we should change "Value 20xx" to just the year value, you can do that by selecting the column and applying "extract after delimiter" like this. As delimited just put a single space:

Then just rename the columns and apply the data type "Whole number" to sales to get this clean tabular data:

Then create a matrix visual like this:

Yes we actually had to unpivot our data just to pivot it in the end again. So in the data model we need everything in clean tabular data while stuff like pivoting happens when we design our report. In Excel the data model is the same as the design of the report, so thats the big difference to Power BI.

You can check all the steps I applied in this sample code (you can just copy and paste it to your query in the advanced query editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktzklUiFDSUTICYlMwjtWBiUdCxUHYDEk8Csg3BmJDiHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"Value 2020" = _t, #"Value 2021" = _t, #"Value 2022" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"(blank)"}, "Attribute", "Value"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Columns", {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"(blank)", "Model"}, {"Attribute", "Year"}, {"Value", "Sales"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}})
in
    #"Changed Type"