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

[Bjorn_C]

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 : 

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

Thanks for your help / input

Regards

Bjorn 

[mahoneypat · Accepted solution]

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

[Jakinta]

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

[watkinnc]

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!

--Nate