Forum Discussion
Duplicate a column with power query
Hi,
I have data like this :
Column1 | Column 2 | Column 3
a d g
b e h
c f i
I want this :
a d g
a e g
a f g
a d h
a e h
a f h
a d i
a e i
a f i
b d g
b e g
...
...
Is it possible in Power BI? (or excel)
Thanks !
Easy Cartesian product regardless of count of columns, for instance, 4 columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoB4nQgzlKK1YlWSgKyUoE4A4izwSLJQFYaEGcCcY5SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), Cols = Table.ToColumns(Source), Cartesian = Table.FromRows( List.Accumulate(List.Skip(Cols), List.Transform(Cols{0}, each {_}),(s,c) => List.TransformMany(s, each c, (x,y) => x & {y})) ) in Cartesian
2 Replies
Easy Cartesian product regardless of count of columns, for instance, 4 columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoB4nQgzlKK1YlWSgKyUoE4A4izwSLJQFYaEGcCcY5SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), Cols = Table.ToColumns(Source), Cartesian = Table.FromRows( List.Accumulate(List.Skip(Cols), List.Transform(Cols{0}, each {_}),(s,c) => List.TransformMany(s, each c, (x,y) => x & {y})) ) in CartesianAnonymous , I would advise crossjoin in DAX
new table =
Crossjoin(Table, Distinct(Table[Column3]) )
