Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 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?

 

table.png

 

Expand the column

 

table2.png

table3.png

 

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"

 

1 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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

another way, all steps done by GUI

 

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

 

image.png

 

image.png

 

 

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

image.png

 

3) add column index to both tables

 

 

image.png

 

4) finally merge tables a and b   on Index column

 

image.png

 

you get this:

 

image.png

 

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

 

image.png

 

Fowmy
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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.

user0009_0-1600239260035.png

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:

user0009_1-1600239324978.png

Then I get the two lines in the first column:

user0009_2-1600239367724.png

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

user0009_3-1600239420529.png

 

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.

 

 

 

 

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
Not applicable

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

image.png

 

 

you get this

image.png

 

and not this which is the "wrong" query

image.png

Anonymous
Not applicable

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors