Add a "Markup" Row to my Table

[Einomi]

Hello,

I have a table of food production and a table of sales of these products. I need to follow on a weekly basis the trend. So, both tables, have a column called "Start of the Week" and it represents the date of the Sunday of that week.

I have merge these two table to have one single table called Report Table. 

 I want to add to this table one row at the bottom. In the column [Attribut] I want the text "Markup" and in all other columns I want the result of Margin / Bought.

Here is the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY67CgJBDEX/ZeoQJq9JprW3sly2EIRVEBdE/9/ZCXaHw703WZZy2r/b/VOgkAKbY5Nk7RWlH2xQzdAjvblgTC8QNjKeLOFYuaywlMv+vB2ygdUxSDnSWdE0Pev/0GBjpJaZcMaQZKdAoTl4vr63x2toHs2Ofb4CKopkE6kTRmLzQPaZrcyobaIIIfeyrj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribut = _t, #"09/02/2025" = _t, #"02/02/2025" = _t, #"26/01/2025" = _t, #"19/01/2025" = _t, #"12/01/2025" = _t, #"05/01/2025" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Attribut", type text}, {"09/02/2025", type text}, {"02/02/2025", type text}, {"26/01/2025", type text}, {"19/01/2025", type text}, {"12/01/2025", type text}, {"05/01/2025", type text}})
in
    #"Type modifié"

Thanks for your time and for your help

@dufoq3 @Akash_Varuna @BA_Pete @ronrsnfld @AlienSx 

[v-ssriganesh]

Hi @Einomi,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Einomi]

Hi @v-ssriganesh I just need to test the other solutions today and hopefully mark it as resolved, thanks

[BA_Pete]

Hi @Einomi ,

All the other guys have provided great solutions for your specific question, so I'll go a slightly different route and suggest the 'correct' way to do this:

Select your [Attribut] column and go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

You now have the optimal table structure to send to your model.

Create a measure like this:

Markup =
VAR __bought =
CALCULATE(
    SUM(yourTable[Value]),
    yourTable[Attribut] = "Bought"
)
VAR __margin =
CALCULATE(
    SUM(yourTable[Value]),
    yourTable[Attribut] = "Margin"
)
RETURN
DIVIDE(__margin, __bought, 0)  // or you can use BLANK() in the last argument if you want

This gives you a correctly structured table that can easily be related into the rest of your model, and a dynamic calculation of Markup that will respond correctly to filters/slicers etc.

Pete

[Einomi]

Thank you so much @BA_Pete for your suggestion. I will keep it mind if I want to use DAX, my data model is somehow complicated so I wanted to try with PQ for this particular challenge. 🙂

[AlienSx]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY67CgJBDEX/ZeoQJq9JprW3sly2EIRVEBdE/9/ZCXaHw703WZZy2r/b/VOgkAKbY5Nk7RWlH2xQzdAjvblgTC8QNjKeLOFYuaywlMv+vB2ygdUxSDnSWdE0Pev/0GBjpJaZcMaQZKdAoTl4vr63x2toHs2Ofb4CKopkE6kTRmLzQPaZrcyobaIIIfeyrj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribut = _t, #"09/02/2025" = _t, #"02/02/2025" = _t, #"26/01/2025" = _t, #"19/01/2025" = _t, #"12/01/2025" = _t, #"05/01/2025" = _t]),
    types = Table.TransformColumns(Source, {"Attribut", (x) as text => x}, (x) as number => Number.From(x)),
    z = types & #table(Value.Type(types), {{"Markup"} & List.Transform(List.Skip(List.Zip(Table.ToList(types, each _))), (x) => x{2} / x{0})})
in
    z

[ronrsnfld]

Somewhat different approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY67CgJBDEX/ZeoQJq9JprW3sly2EIRVEBdE/9/ZCXaHw703WZZy2r/b/VOgkAKbY5Nk7RWlH2xQzdAjvblgTC8QNjKeLOFYuaywlMv+vB2ygdUxSDnSWdE0Pev/0GBjpJaZcMaQZKdAoTl4vr63x2toHs2Ofb4CKopkE6kTRmLzQPaZrcyobaIIIfeyrj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribut = _t, #"09/02/2025" = _t, #"02/02/2025" = _t, #"26/01/2025" = _t, #"19/01/2025" = _t, #"12/01/2025" = _t, #"05/01/2025" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Attribut", type text}, {"09/02/2025", type text}, {"02/02/2025", type text}, {"26/01/2025", type text}, {"19/01/2025", type text}, {"12/01/2025", type text}, {"05/01/2025", type text}}),

//set to useful data types
    #"Type as Number" = Table.TransformColumnTypes(#"Type modifié", 
        List.Transform(List.Skip(Table.ColumnNames(#"Type modifié")), each {_, type number})),

//Add Markup Row
    #"Markups" = let 
            a=Table.RemoveColumns(#"Type as Number","Attribut")
        in 
            [Attribut="Markups"] & Record.FromList(List.Transform(
                List.Zip({Record.FieldValues(a{0}), Record.FieldValues(a{2})}),
                each _{1} / _{0}), List.Skip(Table.ColumnNames(#"Type as Number"))),
    

    #"Add Markups Row" = Table.FromRecords(Table.ToRecords(#"Type as Number") & {Markups})
in
    #"Add Markups Row"

Of course, the visual problem is that all of the values in a given column must have the same data type. Ideally you might want to have the top rows with a currency data type and the Markup row with a percent data type.  But that's not possible in PQ.

Consider unpivoting, then Pivot on the expanded Attribut column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY67CgJBDEX/ZeoQJq9JprW3sly2EIRVEBdE/9/ZCXaHw703WZZy2r/b/VOgkAKbY5Nk7RWlH2xQzdAjvblgTC8QNjKeLOFYuaywlMv+vB2ygdUxSDnSWdE0Pev/0GBjpJaZcMaQZKdAoTl4vr63x2toHs2Ofb4CKopkE6kTRmLzQPaZrcyobaIIIfeyrj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribut = _t, #"09/02/2025" = _t, #"02/02/2025" = _t, #"26/01/2025" = _t, #"19/01/2025" = _t, #"12/01/2025" = _t, #"05/01/2025" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Attribut", type text}, {"09/02/2025", type text}, {"02/02/2025", type text}, {"26/01/2025", type text}, {"19/01/2025", type text}, {"12/01/2025", type text}, {"05/01/2025", type text}}),

//set to useful data types
    #"Type as Number" = Table.TransformColumnTypes(#"Type modifié", 
        List.Transform(List.Skip(Table.ColumnNames(#"Type modifié")), each {_, type number})),

//Add Markup Row
    #"Markups" = let 
            a=Table.RemoveColumns(#"Type as Number","Attribut")
        in 
            [Attribut="Markups"] & Record.FromList(List.Transform(
                List.Zip({Record.FieldValues(a{0}), Record.FieldValues(a{2})}),
                each _{1} / _{0}), List.Skip(Table.ColumnNames(#"Type as Number"))),
    

    #"Add Markups Row" = Table.FromRecords(Table.ToRecords(#"Type as Number") & {Markups}),
    
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Add Markups Row", {"Attribut"}, "Date", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", 
        List.Distinct(#"Unpivoted Other Columns"[Attribut]), "Attribut", "Value", List.Sum),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}},"en-150"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Bought", Currency.Type}, {"Sold", Currency.Type}, {"Margin", Currency.Type}, {"Markups", Percentage.Type}})
        
in
    #"Changed Type1"

Then you can set the column data types more appropriately:

[dufoq3]

Hi @Einomi, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY67CgJBDEX/ZeoQJq9JprW3sly2EIRVEBdE/9/ZCXaHw703WZZy2r/b/VOgkAKbY5Nk7RWlH2xQzdAjvblgTC8QNjKeLOFYuaywlMv+vB2ygdUxSDnSWdE0Pev/0GBjpJaZcMaQZKdAoTl4vr63x2toHs2Ofb4CKopkE6kTRmLzQPaZrcyobaIIIfeyrj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribut = _t, #"09/02/2025" = _t, #"02/02/2025" = _t, #"26/01/2025" = _t, #"19/01/2025" = _t, #"12/01/2025" = _t, #"05/01/2025" = _t]),
    ChangedType = Table.TransformColumnTypes(Source, List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, type number}), "en-US"),
    Ad_MarkupValue = List.TransformMany(List.Skip(Table.ToColumns(ChangedType)), 
        each {_},
        (x,y)=> x & {y{2} / y{0}} ),
    ToTbl = Table.FromColumns({Table.Column(ChangedType, Table.ColumnNames(ChangedType){0}) & {"Markup"}} & Ad_MarkupValue, Value.Type(Table.FirstN(ChangedType, 0)))
in
    ToTbl