Split Row by Value in the Cell

[KOPPUROTU]

Hi Every one,

I want to split the single row into multiple rows based on Value in the Particular cell,

Column	Indicator
A	4
B	2
C	6

Suppose the Above table is the Input I want output like below. 

Column	Indicator
A	4
A	4
A	4
A	4
B	2
B	2
C	6
C	6
C	6
C	6
C	6
C	6

[Rocco_sprmnt21 · Accepted solution]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsozALGcgy0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Indicator = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Indicator", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Column"}, {{"Replica", each Table.Repeat(_, _[Indicator]{0}), type table [Column=nullable text, Indicator=nullable text]}}),
    #"Tabella Replica espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "Replica", {"Indicator"}, {"Indicator"})
in
    #"Tabella Replica espansa"

[v-kelly-msft · Accepted solution]

Hi @KOPPUROTU ,

You could also use below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsozALGcgy0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Indicator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Indicator", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
x = [Indicator]
in
List.Generate(
  ()=>1,
  each _<=x,
  each _+1
)),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom1"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Rocco_sprmnt21 · Accepted solution]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsozALGcgy0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Indicator = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Indicator", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Column"}, {{"Replica", each Table.Repeat(_, _[Indicator]{0}), type table [Column=nullable text, Indicator=nullable text]}}),
    #"Tabella Replica espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "Replica", {"Indicator"}, {"Indicator"})
in
    #"Tabella Replica espansa"