none
Consolidate Stacked Tables RRS feed

  • Question

  • Hi,

    I have 400 tables that have structure as follows

    Table 1 (Att_1)

    Attribute 1     A

    Attribute 2     B

    Attribute 3     C

    Table 2 (Att_2)

    Attribute 1     D

    Attribute 2     E

    Attribute 3     F

    Table 3 (Att_3)

    Attribute 1     G

    Attribute 2     H

    Attribute 3     I

    I would like to consolidate them into this:

    Attribute 1 Attribute 2 Attribute 3

    A B C

    D E F

    G H I




    Thursday, February 25, 2016 6:12 PM

Answers

  • The full code


    let
        Source = Folder.Files("C:\Users\murad.efendiev\Desktop\Loan DB\Loan Forms DEC31"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if Text.Start([Custom.Name],3)="Att" then [Custom.Name] else ""),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom] <> "")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.Item", "Custom.Kind", "Custom"}),
        #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each Table.PromoteHeaders(Table.Transpose([Custom.Data]))),
        #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"LOAN_ID", "Local Loan ID ", "Intercompany ID", "Active/Matured", "New / Amended", "Loan Type", "Syndication", "Subordinated", "Lender", "Manager", "Wholesale Lender", "Currency", "Original Loan Amount (in currency of loan issue)", "Contract Signing Date", "Original Maturity date", "Amended Maturity Date", "Upfront Fee", "Withholding Tax Rate (%)", "Interest Rate Type", "Days Interest per Annum", "Interest Rate (Fixed rate loans only)", "Interest Rate Spread (Floating rate loans only)", "Interest Rate Index (Floating rate loans only)", "Interest Payment Frequency", "Guarantee", "FX Hedging", "Hedge Contract ID", "Collateral Type", "Collateral Posted"}, {"LOAN_ID", "Local Loan ID ", "Intercompany ID", "Active/Matured", "New / Amended", "Loan Type", "Syndication", "Subordinated", "Lender", "Manager", "Wholesale Lender", "Currency", "Original Loan Amount (in currency of loan issue)", "Contract Signing Date", "Original Maturity date", "Amended Maturity Date", "Upfront Fee", "Withholding Tax Rate (%)", "Interest Rate Type", "Days Interest per Annum", "Interest Rate (Fixed rate loans only)", "Interest Rate Spread (Floating rate loans only)", "Interest Rate Index (Floating rate loans only)", "Interest Payment Frequency", "Guarantee", "FX Hedging", "Hedge Contract ID", "Collateral Type", "Collateral Posted"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Name", "Content", "Custom.Name", "Custom.Data"})
    in
        #"Removed Columns1"
    Friday, February 26, 2016 8:13 PM

All replies

  • Where are these 400 tables now? Already in Power Query, in your current workbook or in external sources?

    Imke Feldmann TheBIccountant.com

    Thursday, February 25, 2016 6:58 PM
    Moderator
  • the final table is not accurate

    So I have little experience with Power Query

    This is the specifics:

    I have 20 files located in single folder

    each file has on average 40 tabs

    each tab has 2 tables which have respectively Prefixes "Att_" and "Pmt_" followed by the name of the tab that they are located in. All tables are auto expanding excel table formats. Pmt tables are very easy to consolidate since they are in database table form. But the Att_ tables look like the ones above where Attribute is not a column label but a row label. I can potentially write a VBA code that will consolidate all of them but it won't be as good and flexible as Power Query solution. And since I am going to consolidate Pmt tables with Power Query I'd like to have a macro-free workbook usable by most. What I cannot seem to achieve is taking the source as folder then having the DAX transpose each table first and then consolidate them. Instead Power Query consolidates them first and then lets me transpose. I tried many ways but I failed being a nube. I can share sample files so you can play around if you care to help. 

    Thursday, February 25, 2016 9:23 PM
  • Yes please, sample files are very useful.

    Imke Feldmann TheBIccountant.com

    Thursday, February 25, 2016 9:26 PM
    Moderator
  • Looks like I cannot attach files yet

    But this is how my tables look this one is named Att_Gua_1


    Attribute Value
    LOAN_ID Gua_1
    Local Loan ID  [Enter locally used ID if helpful]
    Intercompany ID [HQ to Complete]
    Active/Matured
    New / Amended  
    Loan Type
    Syndication  
    Subordinated
    Lender Watson Investments B.V.
    Manager Credit Suisse
    Wholesale Lender  
    Currency GTQ
    Original Loan Amount (in currency of loan issue)  
    Contract Signing Date
    Original Maturity date 6/13/2016
    Amended Maturity Date [Only include if original contract has been amended, renewed, etc.]
    Upfront Fee 0.00%
    Withholding Tax Rate (%)
    Interest Rate Type Fixed
    Days Interest per Annum
    Interest Rate (Fixed rate loans only) 12.00%
    Interest Rate Spread (Floating rate loans only)
    Interest Rate Index (Floating rate loans only) [Name of Index Rate, LIBOR, EURIBOR, Central Bank Borrowing Rates]
    Interest Payment Frequency
    Guarantee None
    FX Hedging
    Hedge Contract ID [If selected YES for FX Hedging > Enter swap counterparty transaction ID]
    Collateral Type
    Collateral Posted [i.e. 8% of GLP]

    and I have about 30 of these for instance tha need to look like 

    LOAN_ID Local Loan ID  Intercompany ID Active/Matured New / Amended Loan Type Syndication Subordinated Lender Manager Wholesale Lender Currency Original Loan Amount (in currency of loan issue) Contract Signing Date Original Maturity date Amended Maturity Date Upfront Fee Withholding Tax Rate (%) Interest Rate Type Days Interest per Annum Interest Rate (Fixed rate loans only) Interest Rate Spread (Floating rate loans only) Interest Rate Index (Floating rate loans only) Interest Payment Frequency Guarantee FX Hedging Hedge Contract ID Collateral Type Collateral Posted
    Gua_1 [Enter locally used ID if helpful] [HQ to Complete]     Watson Investments B.V. Credit Suisse   GTQ   ######## [Only include if original contract has been amended, renewed, etc.] 0.00% Fixed 12.00% [Name of Index Rate, LIBOR, EURIBOR, Central Bank Borrowing Rates] None [If selected YES for FX Hedging > Enter swap counterparty transaction ID] [i.e. 8% of GLP]

    Thursday, February 25, 2016 9:33 PM
  • Have a look if this is what you want to do: https://onedrive.live.com/redir?resid=DE165DDF5D02DAFF%2124163

    It transposes before appending. All in one step. And you don't have to pass any of these table to Power Query specifically.


    Imke Feldmann TheBIccountant.com

    Thursday, February 25, 2016 9:57 PM
    Moderator
  • Thank you so much! However how can I rewrite the code so that it will take in all the tables (about 400 of them) that start with "Att_" and combine them the way you did?

    Is it not possible via Power Query?

    Thursday, February 25, 2016 10:32 PM
  • Of course can you do it.

    You have to replace the reference to the internal excel tables to the external ones:

    Code in file:

    = Table.Combine({Table.PromoteHeaders(Table.Transpose(Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content])), Table.PromoteHeaders(Table.Transpose(Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content])), Table.PromoteHeaders(Table.Transpose(Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content]))})

    Your new code:

    = Table.Combine({Table.PromoteHeaders(Table.Transpose(Excel.Workbook(File.Contents("...YourFilepath\ProduktA.xlsx"), null, true){[Item="YourTableName",Kind="Table"]}[Data])), Table.PromoteHeaders(Table.Transpose(Excel.Workbook(File.Contents("...YourFilepath\ProduktB.xlsx"), null, true){[Item="YourTableName",Kind="Table"]}[Data])), Table.PromoteHeaders(Table.Transpose(Excel.Workbook(File.Contents("...YourFilepath\ProduktC.xlsx"), null, true){[Item="YourTableName",Kind="Table"]}[Data]}[Content]))})


    Imke Feldmann TheBIccountant.com


    Thursday, February 25, 2016 10:43 PM
    Moderator
  • Thank you!

    I will try to tweak it. Since your code does not have any "for loops" i think it is not that simple. I'd need to write at least two loops for the files and for the tables. There also should be an if function to separate Att_ tables from Pmt_ tables. Am I missing something?

    Thursday, February 25, 2016 11:21 PM
  • Actually, you might be happier with this method then: http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

    For simple orperation like in my example insted of writing a separate function you just paste in the nested steps into your new column:

    ContentFromFile = Table. AddColumn(PrevStepShowingTableWithFolderContent, "ContentNew", each Table.PromoteHeaders(Table.Transpose([Content]))),


    Imke Feldmann TheBIccountant.com

    Friday, February 26, 2016 6:22 AM
    Moderator
  • Hi,

    The link didn't work. But it looks more like what I was looking for.

    Can you resend the link please?

    Friday, February 26, 2016 2:41 PM
  • Seems to be a problem with their website. Here a different one: http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/


    Imke Feldmann TheBIccountant.com

    Friday, February 26, 2016 4:43 PM
    Moderator
  • Hey I did it. Just needed to be more patient with myself. I did step by step trying to understand the structure of your code. had tens of errors. I created two custom columns. First one filters the necessary tables. The second one transposes and then promotes the headers et voila!! looks so simple and elegant too vs writing a VBA code that would probably eat up all the ram on my PC..

    Thank you very much!!!


    Friday, February 26, 2016 5:07 PM
  • The full code


    let
        Source = Folder.Files("C:\Users\murad.efendiev\Desktop\Loan DB\Loan Forms DEC31"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if Text.Start([Custom.Name],3)="Att" then [Custom.Name] else ""),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom] <> "")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.Item", "Custom.Kind", "Custom"}),
        #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each Table.PromoteHeaders(Table.Transpose([Custom.Data]))),
        #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"LOAN_ID", "Local Loan ID ", "Intercompany ID", "Active/Matured", "New / Amended", "Loan Type", "Syndication", "Subordinated", "Lender", "Manager", "Wholesale Lender", "Currency", "Original Loan Amount (in currency of loan issue)", "Contract Signing Date", "Original Maturity date", "Amended Maturity Date", "Upfront Fee", "Withholding Tax Rate (%)", "Interest Rate Type", "Days Interest per Annum", "Interest Rate (Fixed rate loans only)", "Interest Rate Spread (Floating rate loans only)", "Interest Rate Index (Floating rate loans only)", "Interest Payment Frequency", "Guarantee", "FX Hedging", "Hedge Contract ID", "Collateral Type", "Collateral Posted"}, {"LOAN_ID", "Local Loan ID ", "Intercompany ID", "Active/Matured", "New / Amended", "Loan Type", "Syndication", "Subordinated", "Lender", "Manager", "Wholesale Lender", "Currency", "Original Loan Amount (in currency of loan issue)", "Contract Signing Date", "Original Maturity date", "Amended Maturity Date", "Upfront Fee", "Withholding Tax Rate (%)", "Interest Rate Type", "Days Interest per Annum", "Interest Rate (Fixed rate loans only)", "Interest Rate Spread (Floating rate loans only)", "Interest Rate Index (Floating rate loans only)", "Interest Payment Frequency", "Guarantee", "FX Hedging", "Hedge Contract ID", "Collateral Type", "Collateral Posted"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Name", "Content", "Custom.Name", "Custom.Data"})
    in
        #"Removed Columns1"
    Friday, February 26, 2016 8:13 PM
  •    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each Table.PromoteHeaders(Table.Transpose([Custom.Data]))),

    This above is where your magic happened

    Friday, February 26, 2016 8:14 PM
  • Yes, this saves you from writing it as a full function like this:

    ... your previous steps,
    
    fnTransformAndPromoteHeaders = (Content) =>
    
    let
    TransposeTable = Table.Transpose(Content),
    PromoteHeaders = Table.PromoteHeaders(TransposeTable),
    in PromoteHeaders,
    
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each fnTransformAndPromoteHeaders ([Custom.Data])),

    M is magic - could actually replace loads of macros :-)


    Imke Feldmann TheBIccountant.com


    Saturday, February 27, 2016 5:18 AM
    Moderator