Reduce table in table to columns

Hello Anonymous 

 

check out this dynamically solution. It chooses the first column of your single tables that contains the name "value" and uses it's data for the column. Be aware that the source step it's just here to reproduce your scenario. The transformation you can see in the step "CreateListOfValueColumns "

let
    Source = #table
    (
        type table [Column1=table, 204 = table],
        {
            {
                #table  
                (
                    type table [Column1.ColumnName= text, Column1.Value= any ],
                    {
                        {
                            "name1", "value1"
                        },
                        {
                            "name2", "value2"
                        },
                        {
                            "name3", "value3"
                        }
                    }
                ),
                #table  
                (
                    type table [Column1.ColumnName= text, Column1.Value= any ],
                    {
                        {
                            "name1", "abc"
                        },
                        {
                            "name2", "abcddd"
                        }
                    }
                )
            }
        }
    ),
    CreateListOfValueColumns = Table.TransformRows
    (
        Source,
        (rec)=> Table.TransformColumns
        (
            Record.ToTable(rec),
            {
                {
                    "Value",
                    (tbl)=> Table.Column(tbl,List.Select(Table.ColumnNames(tbl), each Text.Contains(Text.Upper(_), "VALUE")){0})
                }
            }
        )[Value]
    ){0},

    CreateFinalTable = Table.FromColumns(CreateListOfValueColumns,Table.ColumnNames(Source))
in
    CreateFinalTable

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If you need to implement it in your data source let me know

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy