Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Reduce table in table to columns

I use Power Query for Microsoft Excel. I get my data from an API. I transform it and group it. Afterwards, I receive a table which has table entries. I don't need tables there, I need the first colum...
Need Help
Show and Tell
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Actually, it is easy to understand. 

Maybe this example helps you to understand better, what is the expectation:

 

I have a result with exactly two result lines.

Now I expand the first colum, so that the stored table there will be included in the table but only with one column.

And I use the column value:

Then I get the two lines in the first column:

Now I expand the second column, because I need data there and not tables:

 

Now I got 4 lines, instead of two, because for each line the previous line is expanded and that is wrong. 

In the end the results will be multiplied, and not just showing the results of the table columns in one table.

 

 

 

 

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

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