Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"
Solved! Go to 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
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
@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 🙂
⭕ 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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
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