cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KOPPUROTU
Regular Visitor

Split Row by Value in the Cell

Hi Every one,

 

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

ColumnIndicator
A4
B2
C6

 

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

 

ColumnIndicator
A4
A4
A4
A4
B2
B2
C6
C6
C6
C6
C6
C6

 

2 ACCEPTED SOLUTIONS
Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1614156906717.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1614156906717.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors