Results 1 to 8 of 8

Thread: Copying Multiple Columns

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    88
    Articles
    0
    Excel Version
    Office 365

    Copying Multiple Columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 ....
    Column1 Column2 Column3 Column4... X.Column1 X.Column2 X.Column3 X.Column4...
    Data1 Data1 Data1 Data1
    Data2 Data2 Data2 Data2
    xData3 xData3 xData3 xData3
    Data4 Data4 Data4 Data4
    xData5 xData5 xData5 xData5
    xData6 xData6 xData6 xData6

    Becomes this ...
    Column1 Column2 Column3 Column4...
    Data1 Data1 Data1 Data1
    Data2 Data2 Data2 Data2
    xData3 xData3 xData3 xData3
    Data4 Data4 Data4 Data4
    xData5 xData5 xData5 xData5
    xData6 xData6 xData6 xData6

    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.
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    88
    Articles
    0
    Excel Version
    Office 365
    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.
    Oh... by the way, YOU'RE WELCOME!

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    Here's that thread (it might help): https://www.excelguru.ca/forums/show...-Colulmn-Names
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    45
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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
    Attached Files Attached Files

  6. #6
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    106
    Articles
    0
    Excel Version
    Excel 365
    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"

  7. #7
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    106
    Articles
    0
    Excel Version
    Excel 365
    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"

  8. #8
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    88
    Articles
    0
    Excel Version
    Office 365
    Your first query didn't achieve the desired results, but the second one is worth considering over my solution. Thanks!
    Oh... by the way, YOU'RE WELCOME!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •