Forum Discussion

Helper V

1 year ago

Solved

Add a "Markup" Row to my Table

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

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

ronrsnfld

1 year ago

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:

AlienSx

1 year ago

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

9 Replies

dufoq3

Community Champion

1 year ago

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

ronrsnfld

Super User

1 year ago

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"

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:

AlienSx

Super User

1 year ago

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

BA_Pete
Super User
1 year ago
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
  Helper V
  1 year ago
  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. 🙂
v-ssriganesh
Community Support
1 year ago
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
  Helper V
  1 year ago
  Hi v-ssriganesh I just need to test the other solutions today and hopefully mark it as resolved, thanks
  - v-ssriganesh
    Community Support
    1 year ago
    Hi Einomi,
    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank you.
v-ssriganesh
Community Support
1 year ago
Hello Einomi,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

Forum Discussion

Add a "Markup" Row to my Table

9 Replies

Related content

No table to add Lakehouse

[PowerQuery] add (same) column from another table

How to add subtotal in table visual

Add or remove column in Lakehouse table

Power Query - Add column from related table