Results 1 to 1 of 1

Thread: PQ - Clean Data Multiple Workbooks Varying Columns

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool PQ - Clean Data Multiple Workbooks Varying Columns

    (question from the web)
    I am trying to combine data from multiple workbooks with differing number of columns and the data isn't in a table.
    Table1 Table2 Result
    No. 1 No. 1 2 3 4 No. Value
    Name Name1 Name Name1 Name2 Name3 Name4 Name Name1
    DoB DoB1 DoB DoB1 DoB2 DoB3 DoB4 DoB DoB1
    Department Dept1 Department Dept1 Dept1 Dept2 Dept3 Department Dept1
    Name Name2
    DoB DoB2
    Department Dept1
    Name Name3
    DoB DoB3
    Department Dept2
    Name Name4
    DoB DoB4
    Department Dept3

    Code:
    let
        Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Append = Table.Combine({Source1, Source2}),
        Filter = Table.SelectRows(Append, each ([4] <> null)),
        Index = Table.AddIndexColumn(Filter, "Index", 1, 1),
        Unpivot = Table.UnpivotOtherColumns(Index, {"Index", "No."}, "Attribute", "Value"),
        Sort = Table.Sort(Unpivot,{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
        TSC = Table.SelectColumns(Sort,{"No.", "Value"})
    in
        TSC
    Description is poor and examples are not representative, It would be better if there were more attachments with data
    Last edited by sandy666; 02-21-2021 at 07:42 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. Replies: 3
    Last Post: 08-28-2013, 02:02 PM
  2. Replies: 2
    Last Post: 05-28-2013, 05:32 PM
  3. Import Data From Multiple Workbooks To Another
    By Jorrg1 in forum Excel Help
    Replies: 2
    Last Post: 05-13-2013, 05:00 PM
  4. copying data from multiple workbooks into another
    By rahulcoolz99 in forum Excel Help
    Replies: 1
    Last Post: 08-22-2012, 09:19 PM
  5. split data into multiple workbooks with 3 conditions.
    By malaionfun in forum Excel Help
    Replies: 5
    Last Post: 05-11-2012, 11:26 AM

Posting Permissions

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