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 |
Solved! Go to 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"
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!
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!
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"
Check out new user group experience and if you are a leader please create your group
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
User | Count |
---|---|
31 | |
28 | |
28 | |
11 | |
9 |
User | Count |
---|---|
61 | |
51 | |
39 | |
19 | |
13 |