Create Rows from a value

[WinLou]

Greetings, 

I've hit a roadblock and I'm sure this is a simple one I just can't seem to figure it out.

I have a table with values like this:

Row       Subject      Value

1 - - - - Math - - - - 4            

2 - - - - Science - - -1

3 - - - - Explosives - 4

I'd like to use the number in the value column to create rows to create this:

Row       Subject      Value

1 - - - - Math - - - - 4 

2 - - - - Math - - - - 4   

3 - - - - Math - - - - 4   

4 - - - - Math - - - - 4              

5 - - - - Science - - -1

6 - - - - Explosives - 4

7 - - - - Explosives - 4

8 - - - - Explosives - 4

9 - - - - Explosives - 4

Thank you for your time and any assistance.

[TeigeGao · Accepted solution]

Hi @WinLou ,

The first method is creating a count column, some changes to @Mariusz 's reply:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNLMkAUiZKsTrRSkZAVnByZmpeciqQZQgWMwayXCsKcvKLM8tSiyFKYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Subject = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Subject", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Values", each {1..[Value]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Values"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Values", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Values", "Row"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Row", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Row", "Subject", "Value"})
in
    #"Reordered Columns"

Another method is using the LIST.REPEAT() function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNLMkAUiZKsTrRSkZAVnByZmpeciqQZQgWMwayXCsKcvKLM8tSiyFKYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Subject = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Subject", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Values", each List.Repeat({[Subject]},[Value])),
    #"Expanded Values" = Table.ExpandListColumn(#"Added Custom", "Values"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Values",{"Row", "Subject"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Values", "Value"})
in
    #"Reordered Columns"

The result will like below:

Best Regards,

Teige

[WinLou]

Thank you!

[Mariusz]

Hi @WinLou 

Pleases see the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUvBNLMlQUNJRMlGK1QFyg5MzU/OSU0EihhAR14qCnPzizLLUYqiyWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Subject = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Subject", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Values", each {1..[Value]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Values"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Values", Int64.Type}})
in
    #"Changed Type1"

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @WinLou ,

Please check the video below it has the way to setup what you need:

https://www.youtube.com/watch?v=6DCeoGulnB0

If you need any help please tell me.

Regards,

MFelix