Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.