locked
How to consolidate several sheets with different columns in a single sheet RRS feed

  • Question

  • I have several workbooks with several sheets each one with following format each sheet. I want to consolidate all pairs of column of each sheet in a single pair of columns in one sheet, for exmaple if there are values in A:B, C:D and E:F I want the data in those pairs of columns in A:B and in 3rd column the sheet name. 

    Is possible to do this in Power Query?  at least for all sheets of a single Workbook?

    I import the workbook but I don't know how to select even number of columns and then merge the columns.

    My data is like below.

    Sheet1 --> Has 9 columns but data useful is in columns that go in pairs, A:B, C:D, E:F. For E:F only first2 rows are useful since 3rd row is not in pair value, I mean, E3 doesn't have an F3 value.

    2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ 2434 XYZ
    1 3192 4 5301 7 1717
    2 4013 5 1467 8 4112
    3 4894 6 5271 9

    Sheet2 --> Has 8 columns but data useful is again columns that go in pairs, A:B, C:D

    7735 7735 7735 7735 7735 7735 7735 7735
    1 3773 7 1292
    2 1942 8 4769
    3 1611 8668
    4 4153
    5 7790
    6 5442

    Sheet3 --> Has 8 columns and data useful is A:B, C:D

    8873 8873 8873 8873 8873 8873 8873
    1 1122 4 7101
    2 5648 5 2213
    3 9093 6 1112

    Output desired is like this:

    1 3192 2434 XYZ
    2 4013 2434 XYZ
    3 4894 2434 XYZ
    4 5301 2434 XYZ
    5 1467 2434 XYZ
    6 5271 2434 XYZ
    7 1717 2434 XYZ
    8 4112 2434 XYZ
    1 3773 7735
    2 1942 7735
    3 1611 7735
    4 4153 7735
    5 7790 7735
    6 5442 7735
    7 1292 7735
    8 4769 7735
    1 1122 8873
    2 5648 8873
    3 9093 8873
    4 7101 8873
    5 2213 8873
    6 1112 8873

    Thanks in advance for any help.





    • Edited by cgkmal Monday, April 8, 2019 6:16 PM
    Monday, April 8, 2019 5:55 PM

Answers

  • Sorry cgkmal,

    I am in the Insider Preview program. I forgot that this function is not officially available yet.
    So....just change fxPairs function to 
        fxPairs = (t as table) =>
            let
                #"Removed Top Rows" = Table.Skip(t,1),
                Custom2 = if Number.IsOdd(Table.ColumnCount(#"Removed Top Rows")) then Table.RemoveColumns(#"Removed Top Rows", List.Last(Table.ColumnNames(#"Removed Top Rows")) ) else #"Removed Top Rows",
                Headers = Table.FromColumns({Table.ColumnNames(Custom2)}),
                #"Added Index" = Table.AddIndexColumn(Headers, "Index", 0, 1),
                #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
                Lists = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Lists", each _[Column1], type list}})[Lists],
                TransformLists = List.Transform(Lists, each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Custom2, _)),1)),
                Combine = Table.Combine(TransformLists),
                Filter = Table.SelectRows(Combine, each ([Column1] <> null) and ([Column2] <> null))
            in
                Filter,

    • Marked as answer by cgkmal Wednesday, April 10, 2019 6:01 PM
    Wednesday, April 10, 2019 9:26 AM

All replies

  • Excel 2010 to 2019 Power Query (aka Get & Transform)
    Consolidate Tables.
    Delete items not paired.
    http://www.mediafire.com/file/y6mvg4fa3m7f1dy/04_08_19a.xlsx/file
    http://www.mediafire.com/file/pzjuvk7u1v2lame/04_08_19a.pdf/file

    Tuesday, April 9, 2019 1:00 AM
  • Hello Herbert,

    Thanks for the answer, but it seems your solution would work if previously the data in each table is defined as "Table".

    I'd like to know how to import the workbook and apply the same code for all sheets, that could be more than 100 in a book, and the merge them in a single sheet.

    For example, If I make a query from workbook look like this for one sheet.

    let
        Source = Excel.Workbook(File.Contents("D:\Files\Input.xlsx"), null, true),
        #"8873_Sheet" = Source{[Item="8873",Kind="Sheet"]}[Data]
    in
        #"8873_Sheet"

    I hope make sense.

    Tuesday, April 9, 2019 2:38 AM
  • Pass.
    Three is my limit.

    Tuesday, April 9, 2019 4:53 AM
  • Pass.
    Three is my limit.

    I understand, Thanks for your help anyway.
    Tuesday, April 9, 2019 5:29 AM
  • Hi cgkmal,

    Please, provide an example file.

    Tuesday, April 9, 2019 8:55 AM
  • Hi cgkmal,

    Please, provide an example file.

    Hello Bill,

    Here is the link for sample file, (only remove the spaces)

    https : // www . dropbox . com /s/5lnuzu0997em28b/MergeCol.xlsx?raw=1

    Thanks for the help

    Tuesday, April 9, 2019 1:27 PM
  • Try this code below: (based on your MergeCol.xlsx file)

    let
        fxPairs = (t as table) =>
            let
                #"Removed Top Rows" = Table.Skip(t,1),
                Custom2 = if Number.IsOdd(Table.ColumnCount(#"Removed Top Rows")) then Table.RemoveColumns(#"Removed Top Rows", List.Last(Table.ColumnNames(#"Removed Top Rows")) ) else #"Removed Top Rows",
                SplitedLists = Table.Combine(List.Transform(List.Split(Table.ColumnNames(Custom2),2), each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Custom2, _)),1)   )),
                #"Filtered Rows1" = Table.SelectRows(SplitedLists, each ([Column1] <> null) and ([Column2] <> null))
            in
                #"Filtered Rows1",
    
    
        Source = Excel.Workbook(File.Contents("PathToYourFile.xlsx"), null, true),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Name] <> "Output")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data", "Name"}),
        #"Added Custom1" = Table.TransformColumns(#"Removed Other Columns", {{"Data", each fxPairs(_)}}),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom1", "Data", {"Column1", "Column2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}",{{"Column1", Int64.Type}, {"Column2", Int64.Type}})
    in
        #"Changed Type"

    Tuesday, April 9, 2019 9:51 PM
  • Hi Bill,

    I've tried your solution, but I receive some errors.

    first step (fxPairs):

    Expression.Error: The name 'List.Split' wasn't recognized.  Make sure it's spelled correctly.

    6th step (Expanded {0}):

    Expression.Error: The import List.Split matches no exports. Did you miss a module reference?

    Last step (Changed Type):

    Expression.Error: The import List.Split matches no exports. Did you miss a module reference?

    I'm using Excel 2016 just in case it matters.

    Tuesday, April 9, 2019 11:39 PM
  • Sorry cgkmal,

    I am in the Insider Preview program. I forgot that this function is not officially available yet.
    So....just change fxPairs function to 
        fxPairs = (t as table) =>
            let
                #"Removed Top Rows" = Table.Skip(t,1),
                Custom2 = if Number.IsOdd(Table.ColumnCount(#"Removed Top Rows")) then Table.RemoveColumns(#"Removed Top Rows", List.Last(Table.ColumnNames(#"Removed Top Rows")) ) else #"Removed Top Rows",
                Headers = Table.FromColumns({Table.ColumnNames(Custom2)}),
                #"Added Index" = Table.AddIndexColumn(Headers, "Index", 0, 1),
                #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
                Lists = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Lists", each _[Column1], type list}})[Lists],
                TransformLists = List.Transform(Lists, each Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Custom2, _)),1)),
                Combine = Table.Combine(TransformLists),
                Filter = Table.SelectRows(Combine, each ([Column1] <> null) and ([Column2] <> null))
            in
                Filter,

    • Marked as answer by cgkmal Wednesday, April 10, 2019 6:01 PM
    Wednesday, April 10, 2019 9:26 AM
  • Sorry cgkmal,

    I am in the Insider Preview program. I forgot that this function is not officially available yet.
    So....just change fxPairs function to 


    Hello Bill,


    No problem. I tested and this time it works just perfect! Thanks a lot.

    I tested in a file with 5 sheets and the output has 764 rows. My only concern is when I click on "Close and Load" it appears 20 MB from File.xslx, 35 MB, up to 100MB from File.xslx and when stops remains 764 rows only on left pane. My concern is because there are files that have more than 100 sheets and could be easily more than 10,000 rows in final output.

    By the way, what is the "Insider Preview Program"? hehe

    Best rehards

    Wednesday, April 10, 2019 6:01 PM