Forum Discussion
Making a new column using values from an existing column
Hi,
If I have a column of length 21 with certain values, like this:
a
b
c
d
etc.
And I want to use these values to make a column of length 84, where each value of the column of length 21 is repeated four times after going to the next value, like this:
a
a
a
a
b
b
b
b
c
c
c
c
etc.
Is this in any way possible?
Thanks!
Hi lhaz,
Based on my test, you can refer to below steps in query editor:
1.Right click the column that you want to duplicate and click the “Duplicate Column” for four times.
2.
2.Choose the whole table and unpivot the columns, delete the extra column and you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
6 Replies
Hi lhaz,
Based on my test, you can refer to below steps in query editor:
1.Right click the column that you want to duplicate and click the “Duplicate Column” for four times.
2.
2.Choose the whole table and unpivot the columns, delete the extra column and you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi,
I have a table with a column of length 21 containing certain values, like this:
a
b
c
d
etc.
And I want to create a column of length 84 where each value from the column of length 21 is repeated four times before going to the next, like this:
a
a
a
a
b
b
b
b
c
c
c
c
etc.
Is this in any way possible?
Thanks!
I guess you are looking to repeat each value to repeat 4 times as seperate row, correct?
Hi lhaz,
could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi Daniel,
Yes my problem has been solved, thank you for the help!Regards,
Laura
Hi,
The following M code in the Query Editor will work
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From(4)}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
#"Removed Columns"Hope this helps.
