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
Bjorn_C
Frequent Visitor

Multiply each single row with a number of specific cell of that row

Hi everyone 

 

I am trying to convert a table in the following way : 

Every row needs to be multiplied "x" times, and "x" equals a value in a cell of that row.

The example belows will help I guess 

 

The starting table is the following : 

 

Bjorn_C_0-1624048104856.png

 

 

The result needs to a table in which the first row of the initial tabel appears 10 times;  and the second line of the inital table appears 2 times

 

Bjorn_C_1-1624048174702.png

 

Thanks for your help / input

 

Regards

Bjorn 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Here's one way to do it in the query editor, by adding a simple List column using the Number column.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyBDI980B8A6VYHYiUEULKv7QEJKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Type = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Number]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"}),
    #"Rounded Up" = Table.TransformColumns(#"Removed Columns",{{"Number", each 1, Int64.Type}})
in
    #"Rounded Up"

 

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

Here's one way to do it in the query editor, by adding a simple List column using the Number column.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyBDI980B8A6VYHYiUEULKv7QEJKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Type = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Number]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"}),
    #"Rounded Up" = Table.TransformColumns(#"Removed Columns",{{"Number", each 1, Int64.Type}})
in
    #"Rounded Up"

 

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Jakinta
Solution Sage
Solution Sage

Here is another aproach. Try this in blank query and adjust accordingly.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaqoABJGSrE60UpOQFZlJZAwVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Number = _t]),
    Cols = Table.ColumnNames(Source),
    Tables = Table.ExpandTableColumn(Table.SelectColumns(Table.AddColumn(Source, "x", each Table.FromRows(List.Repeat({List.RemoveLastN(Record.ToList(_),1)&{1}}, Number.From([Number])), Cols)), "x"), "x", Cols)
in
    Tables
Anonymous
Not applicable

This was actually a pretty good idea you came up with.  Here's how I would implement it:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyADI980B8A6VYnWglJyDTCCHlX1oCElCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Product"}, {"Column2", "Date"}, {"Column3", "Type"}, {"Column4", "Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Number", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product", "Date", "Type"}, {{"Rows", each List.Max([Number]), type nullable number}, {"Details", each _, type table [Product=nullable text, Date=nullable date, Type=nullable text, Number=nullable number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"Details", each Table.Repeat(_, [Number]{0})}}),
#"Removed Other Columns" = Table.SelectColumns(Custom1,{"Details"}),
#"Expanded Details" = Table.ExpandTableColumn(#"Removed Other Columns", "Details", {"Product", "Date", "Type", "Number"}, {"Product", "Date", "Type", "Number"})
in
#"Expanded Details"

 

The key is the Table.Repeat function on the column of tables, via Table.Transform Columns, and to use the nested "Number"Column as the parameter, or number to repeat [Number]{0}, or Number column, first row.

Using Table.TransformColumns instead of always using Table.AddColumns all the time.  And the "each" keyword works in that context as well!

watkinnc_0-1624051056049.png

 

 

--Nate

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.