Copying Multiple Columns

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
Seems all my questions tend to be working with multiple columns.

I have 52 columns laid out in this fashion.
If Column1 is null, I need to copy all the 26 X.columns over to the 26 main columns. (Yes the columns are named the same, the X.Columns have a prefix)

So this ....
Column1Column2Column3Column4...X.Column1X.Column2X.Column3X.Column4...
Data1Data1Data1Data1
Data2Data2Data2Data2
xData3xData3xData3xData3
Data4Data4Data4Data4
xData5xData5xData5xData5
xData6xData6xData6xData6

Becomes this ...
Column1Column2Column3Column4...
Data1Data1Data1Data1
Data2Data2Data2Data2
xData3xData3xData3xData3
Data4Data4Data4Data4
xData5xData5xData5xData5
xData6xData6xData6xData6

I'm trying to avoid using pivots as there can be millions of rows, and my computer seems to choke when pivoting the much data.
Trying to come up with a TransformFields or TransformColumns routine, but having a tough time getting a list of values from the X.Columns.
 
Here's what I came up with, but that ReplaceValues step still looks a bit convoluted.

Code: 
let    
    Source = Excel.CurrentWorkbook(){[Name="CopyMultiple"]}[Content],
    ColumnList = List.FirstN(Table.ColumnNames(Source),List.PositionOf(Table.ColumnNames(Source),"X.Column1")),
    ReplaceValues = Table.FromRecords(Table.TransformRows(Source, (row) as record => if row[X.Column1] <> null then Record.TransformFields(row,List.Zip({ColumnList,List.Transform(ColumnList, (item) => each Record.Field(row,"X." & item))})) else row)),
    DeleteColumns = Table.SelectColumns(ReplaceValues,ColumnList)
in
    DeleteColumns

I'm thinking a Merge of the columns would be a better alternative as it would eliminate the need of deleting the columns, but this gets the job done for now.
 
Pivoting would be easier, of course ...

I am curious to know where the prefix came from in the first place. There was another thread yesterday or the day before that might help - I'll see if I can find it.
 
You could convert the table to a list, remove the strings of consecutive commas that result from the empty cells, then convert the list back to a table. I couldn't find a way of doing this without converting all the columns to text first.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ColumnList = List.FirstN(Table.ColumnNames(Source),List.PositionOf(Table.ColumnNames(Source),"xA")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"xA", type text}, {"xB", type text}, {"xC", type text}, {"xD", type text}}),
    ToList = Table.ToList(#"Changed Type"),
    RemoveCommas = List.ReplaceValue(ToList,",,,,","",Replacer.ReplaceText),
    #"Converted to Table" = Table.FromList(RemoveCommas, Splitter.SplitTextByDelimiter(","), 4, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    RenameColumns = Table.RenameColumns(#"Changed Type1",List.Zip({Table.ColumnNames(#"Changed Type1"), ColumnList}))
in
    RenameColumns

Norm
 

Attachments

  • ToListAndBack.xlsx
    17.7 KB · Views: 20
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Atrybut", "Wartość"),
    Transform = Table.TransformColumns(#"Unpivoted Other Columns", {{"Atrybut", each if Text.StartsWith(_, "x") then Text.AfterDelimiter(_, "x") else _}}),
    #"Pivoted Column" = Table.Pivot(Transform, List.Distinct(Transform[Atrybut]), "Atrybut", "Wartość"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"
 
I didn't notice your remark about pivoting
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ColNames = Table.ColumnNames(Source),
    ColCount = List.Count(ColNames)/2,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    Table1 = Table.SelectColumns(#"Added Index", List.FirstN(ColNames, ColCount) & {"Index"}),
    Table2 = Table.SelectColumns(#"Added Index", List.LastN(ColNames, ColCount) & {"Index"}),
    RenColTable2 = Table.RenameColumns(Table2,List.Zip({List.LastN(ColNames, ColCount), List.FirstN(ColNames, ColCount)})),
    #"Appended Query" = Table.Combine({Table1, RenColTable2}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([A] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"
 
Your first query didn't achieve the desired results, but the second one is worth considering over my solution. :) Thanks!
 
You must log in or register to reply here.
Back
Top