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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors