none
how to expand the rows? RRS feed

  • Question

  • hi, 

    I have two tables. they do not contain any common dimension:

    T1:

    Asset / Depreciation / capitalisation date

    A1  /  12 / 20190101

    A2 / 10  / 20181020

    A3 / 12 / 20170920

    T2:

    Depreciation class

    DPGR

    DPLC

    DPTX

    Now, I would like to land with the followng table as result:

    Depreciation Class /Asset / Depreciation / capitalisation date

    DPGR / A1  /  12 / 20190101

    DPLC / A1  /  12 / 20190101

    DPTX / A1  /  12 / 20190101

    DPGR / A2 / 10  / 20181020

    DPLC/ A2 / 10  / 20181020

    DPTX / A2 / 10  / 20181020

    DPGR / A3 / 12 / 20170920

    DPLC/ A3 / 12 / 20170920

    DPTX / A3 / 12 / 20170920

    looks quite easy, how to do this in power query?

    thanks a lot for your advices

    Friday, September 20, 2019 5:28 PM

Answers

  • The example was done using tables in Excel with names Table1 and Table2. Once you import the tables into Power Query (Data-->FromTable/Range), you select Table1 in Power Query, go to the Advanced Editor (Home-->Advanced Editor), and paste the code in the advanced editor.

    In the addedCustom step, we simply added the list from Table2. A list can be specified in two ways:

    1) TableName[ColumnName] - I used this method in the code
    2) Table.Column(TableName, "ColumnName")

    The list is simply the list or values in the specified column.

    • Marked as answer by Draszor Saturday, September 21, 2019 7:45 AM
    Friday, September 20, 2019 10:30 PM

All replies

  • You are correct - it's quite easy. Simply add a custom column to Table1(T1) which is the list in Table2(T2), and then expand the list, e.g.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedCustom = Table.AddColumn(Source, "Depreciation class", each Table2[Depreciation class]),
        expandedDepreciationClass = Table.ExpandListColumn(addedCustom, "Depreciation class"),
        reorderedColumns = Table.ReorderColumns(expandedDepreciationClass,{"Depreciation class", "Asset", "Depreciation", "capitalisation date"})
    in
        reorderedColumns

    Friday, September 20, 2019 6:28 PM
  • Thank you. How can I use the code you pasted? I noticed that some answers contain piece of code but I have no idea how to use it in practice. Power query is really cool I just need to understand some basics. Thank you for your help Draszor
    Friday, September 20, 2019 7:43 PM
  • Can you please describe the steps I need to perform to add the custom column being the list in Table2? 

    Having Table1 in power query editor, I go to Add Column tab, click Custom column and I do not know how there add the list from table 2. I need to add somehow the Table2 first I think, but how?

    thanks

    Friday, September 20, 2019 8:05 PM
  • The example was done using tables in Excel with names Table1 and Table2. Once you import the tables into Power Query (Data-->FromTable/Range), you select Table1 in Power Query, go to the Advanced Editor (Home-->Advanced Editor), and paste the code in the advanced editor.

    In the addedCustom step, we simply added the list from Table2. A list can be specified in two ways:

    1) TableName[ColumnName] - I used this method in the code
    2) Table.Column(TableName, "ColumnName")

    The list is simply the list or values in the specified column.

    • Marked as answer by Draszor Saturday, September 21, 2019 7:45 AM
    Friday, September 20, 2019 10:30 PM
  • thank you. Now I got it. I missed the point that in AddColumns --> Custom Column I can write the sytnax you indicated TableName[ColumnName]  in the code into Custom Column formula window
    Saturday, September 21, 2019 7:47 AM