Reduce table in table to columns

[Anonymous]

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 column of the table in the table. 

The first column of the column is only the name of the column, so I need to remove the first column.

I tried to use the "Extend columns" functionality (symbol with the two arrows in the header). But then I get the two columns in the first column. Doing the same in the second column, brings up lines of the first column multiplied with number of lines of the second columns. 

I just want to see the columns in a table withe original number of results.

How can I achieve this?

Expand the column

My code after the API request. This request is not provided.

result = Quelle[result],
    rowsList = result[rowsList],
    #"In Tabelle konvertiert" = Table.FromList(rowsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandListColumn(#"In Tabelle konvertiert", "Column1"),
    #"Erweiterte Column2" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1", {"value", "columnName"}, {"Column1.value", "Column1.columnName"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Erweiterte Column2",{"Column1.columnName", "Column1.value"}),
    #"Gruppierte Zeilen" = Table.Group(#"Neu angeordnete Spalten", {"Column1.columnName"}, {{"Anzahl", each _, type table [Column1.columnName=nullable text, Column1.value=nullable text]}}),
    #"Transponierte Tabelle2" = Table.Transpose(#"Gruppierte Zeilen"),
    #"Höher gestufte Header2" = Table.PromoteHeaders(#"Transponierte Tabelle2", [PromoteAllScalars=true]),
    #"Erweiterte 1" = Table.ExpandTableColumn(#"Höher gestufte Header2", "11", {"Column1.value"}, {"11.Column1.value"})
in
    #"Erweiterte 1"

[Anonymous]

another way, all steps done by GUI

1) group by name and make a duplicate. Let call the "original" a and the "copy" b.

2) drill down both tables  of column all (Table aaa on query a and table bbb on query b)

3) add column index to both tables

4) finally merge tables a and b   on Index column

you get this:

then expand tables on column b  and delete the unnecessary columns and you are done

[Fowmy]

@Anonymous 

Do you need to extract only the first column from each table and combine it as one table?

Sample Data will be great.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

[Fowmy]

@Anonymous 


Not very clear about your expected results.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

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 · Accepted solution]

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]

I didn't read the last post, but as far as I understand from the first, I propose this idea to you

use/adapt this query

let
    colN=Table.ColumnNames(correct),
    colnames=List.Transform(Table.ColumnNames(correct[aaa]{0}), each colN{0}&"."&_) &List.Transform(Table.ColumnNames(correct[bbb]{0}), each colN{1}&"."&_),
    Source = Table.FromColumns(Table.ToColumns(correct[aaa]{0})&Table.ToColumns(correct[bbb]{0}),colnames)
in
    Source

where the table correct is the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMgRhQ6VYnWilpKQkIMcIhI3AAhAVxiBsjCRgAsImSFpMQdhUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, val1 = _t, val2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"val1", Int64.Type}, {"val2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"all", each _, type table [name=nullable text, val1=nullable number, val2=nullable number]}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"aaa", type any}, {"bbb", type any}})
in
    #"Changed Type1"

then from this

you get this

and not this which is the "wrong" query

[Anonymous]

@Fowmy I added more details and code to my post.