none
Fill one table with data from several others of equal structure under the term RRS feed

  • Question

  • There's one table-formatted range "ToDoList" to be filled from multiple source-tables of the same structure: the text from "ToDo" column is taken if the condition from "condition" column is met (=1). There're 2 source tables but I want the solution to be expandable for more of them.

    I thought it would be easy to make it pure formula but expanding it on multiple source-tables would make it too large and hard to comprehend.

    I've met something like merging tables from different files or lists of workbook, but there should be done some user's function that I can't do. Could you help me with that?

    Table here: https://yadi.sk/i/nBaHL_5XR11Hng 

    Note that this is Russian version of Excel so the IF formula is looking like ЕСЛИ with ";" instead of ",", sorry

    Sunday, March 8, 2020 11:30 AM

Answers

  • Hi Crowberg

    #1 On a sheet you can hide later setup a Table as below and name it ie. TargetTables

    #2 Name your Congrats, Shopping, ...tables (they currently have their default names - not easy to put in place the above table)
    #3 In the above table list all the Table names you want to consider for your final ToDoList (here I added a fake Foo table)
    #4 Use the following code to create query TargetTables a load that query as Connection only

    let
        Source = Excel.CurrentWorkbook(){[Name="TargetTables"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Table_Name", type text}}),
        RemovedNulls = Table.SelectRows(ChangedType, each ([Table_Name] <> null))
    in
        RemovedNulls

    #5 Use the following code to create query ToDoList and load it to a worksheet:

    let
        TablesInCurrentWorkbook = Excel.CurrentWorkbook(),
        TargetTablesOnly = Table.Join(TargetTables,"Table_Name", TablesInCurrentWorkbook,"Name"),
        CombinedNestedTables = Table.Combine(TargetTablesOnly[Content]),
        FilteredCondition = Table.SelectRows(CombinedNestedTables, each ([condition] = 1)),
        ToDoColumnOnly = Table.SelectColumns(FilteredCondition,{"ToDo"}),
        ToDoTypedText = Table.TransformColumnTypes(ToDoColumnOnly,{{"ToDo", type text}})
    in
        ToDoTypedText

    Your workbook updated with the above queries is avail. here
    Nice day...

    • Marked as answer by Crowberg Sunday, March 8, 2020 4:49 PM
    Sunday, March 8, 2020 1:10 PM

All replies

  • Hi Crowberg

    #1 On a sheet you can hide later setup a Table as below and name it ie. TargetTables

    #2 Name your Congrats, Shopping, ...tables (they currently have their default names - not easy to put in place the above table)
    #3 In the above table list all the Table names you want to consider for your final ToDoList (here I added a fake Foo table)
    #4 Use the following code to create query TargetTables a load that query as Connection only

    let
        Source = Excel.CurrentWorkbook(){[Name="TargetTables"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Table_Name", type text}}),
        RemovedNulls = Table.SelectRows(ChangedType, each ([Table_Name] <> null))
    in
        RemovedNulls

    #5 Use the following code to create query ToDoList and load it to a worksheet:

    let
        TablesInCurrentWorkbook = Excel.CurrentWorkbook(),
        TargetTablesOnly = Table.Join(TargetTables,"Table_Name", TablesInCurrentWorkbook,"Name"),
        CombinedNestedTables = Table.Combine(TargetTablesOnly[Content]),
        FilteredCondition = Table.SelectRows(CombinedNestedTables, each ([condition] = 1)),
        ToDoColumnOnly = Table.SelectColumns(FilteredCondition,{"ToDo"}),
        ToDoTypedText = Table.TransformColumnTypes(ToDoColumnOnly,{{"ToDo", type text}})
    in
        ToDoTypedText

    Your workbook updated with the above queries is avail. here
    Nice day...

    • Marked as answer by Crowberg Sunday, March 8, 2020 4:49 PM
    Sunday, March 8, 2020 1:10 PM
  • Thanks, Lz._, looks nice!

    Trying to learn it now.

    Have a nice day too.

    Sunday, March 8, 2020 4:49 PM