Forum Discussion
Unpivot multile columns
Hi all,
I would like to unpivot multiple columns but have issues with the result.
My table looks like:
I would like to get to a table which looks like:
I tried to unipivot these, first all together then separately by "P" and "P amount" but the total in the end is not higher than the real total.
Any help appreciated.
Best regards
Hello ITManuel
for sure you have to integrate my query in yours, but you have also to replace my datasource with your existing query. I can not test your query, but i try to fix it.
let Source = Excel.Workbook(File.Contents("C:\Users\Manuelm\Desktop\Project reporting\GPC\GPC -- Invoices and Payments.xlsx"), null, true), GPCInvPayStatus_Table = Source{[Item="GPCInvPayStatus",Kind="Table"]}[Data], #"Removed Columns" = Table.RemoveColumns(GPCInvPayStatus_Table,{".", "Fälligkeit", "Zahlung", "Fäl.", "Zahl.Art", "Zahlungsart"}) ProjectColumns = List.Select(Table.ColumnNames(#"Removed Columns"), each not Text.Contains(_, "amount")), AmountColumns = List.Select(Table.ColumnNames(#"Removed Columns"), each Text.Contains(_, "amount")), GetProjectColumns = List.Combine(List.Transform(ProjectColumns, each Table.Column(#"Removed Columns", _))), GetAmountColumns = List.Combine(List.Transform(AmountColumns, each Table.Column(#"Removed Columns", _))), CreateFinalTable = Table.FromColumns({GetProjectColumns,GetAmountColumns }, {"Project", "Project amount"}) in CreateFinalTable
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
7 Replies
Hi ITManuel
Your query is unclear, can you please share the pbix file or data in excel and also share the expected output you want to achieve.
Refer to article below, to get your queries answered in timely manner.
Thanks,
Ankit
Hi AnkitKukreja ,
sorry for not beeing clear.
I have the data and the pbix file under the following link. https://we.tl/t-RSobOxH3aR
Basically I have a table which contains invoices, their related total amount and the portions of these invoices for multiple projects.
P1 - P7 & Not assigned to any project.
For these P1 - P7 & Not assigned the table has 16 colums, 2 for each project or not assigned.
I would like to have 2 columns out of these 16, 1 for Project ID (P1, P2.....) and one for the related values.
Best regards
Hello ITManuel
check out this solutuion. First create 2 different lists, one for project one for amount (i made it dynamically, using Text.Contains and look for amount. With this lists, create again 2 lists of all columns with amount and for project. Last step is to create a new table with Table.FromColumns.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABJOQGxkoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, #"P1 amount" = _t, P2 = _t, #"P2 amount" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"P1", type text}, {"P1 amount", Int64.Type}, {"P2", type text}, {"P2 amount", Int64.Type}}), ProjectColumns = List.Select(Table.ColumnNames(#"Changed Type"), each not Text.Contains(_, "amount")), AmountColumns = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "amount")), GetProjectColumns = List.Combine(List.Transform(ProjectColumns, each Table.Column(#"Changed Type", _))), GetAmountColumns = List.Combine(List.Transform(AmountColumns, each Table.Column(#"Changed Type", _))), CreateFinalTable = Table.FromColumns({GetProjectColumns,GetAmountColumns }, {"Project", "Project amount"}) in CreateFinalTableCopy paste this code to the advanced editor in a new blank query to see how the solution works.
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
JimmyHi Jimmy801 ,
how do I have to integrate this into the existing query? You were mentioning a new blank query, but I'll have in any case the data request in the advanced editor, right?
I tried this, but it gives a syntax error in the "Source = Table.FromRows(Jso........ line.
let
Source = Excel.Workbook(File.Contents("C:\Users\Manuelm\Desktop\Project reporting\GPC\GPC -- Invoices and Payments.xlsx"), null, true),
GPCInvPayStatus_Table = Source{[Item="GPCInvPayStatus",Kind="Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(GPCInvPayStatus_Table,{".", "Fälligkeit", "Zahlung", "Fäl.", "Zahl.Art", "Zahlungsart"})Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABJOQGxkoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, #"P1 amount" = _t, P2 = _t, #"P2 amount" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"P1", type text}, {"P1 amount", Int64.Type}, {"P2", type text}, {"P2 amount", Int64.Type}}),ProjectColumns = List.Select(Table.ColumnNames(#"Changed Type"), each not Text.Contains(_, "amount")),
AmountColumns = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "amount")),
GetProjectColumns = List.Combine(List.Transform(ProjectColumns, each Table.Column(#"Changed Type", _))),
GetAmountColumns = List.Combine(List.Transform(AmountColumns, each Table.Column(#"Changed Type", _))),
CreateFinalTable = Table.FromColumns({GetProjectColumns,GetAmountColumns }, {"Project", "Project amount"})in
#"Removed Columns"
CreateFinalTableHello ITManuel
for sure you have to integrate my query in yours, but you have also to replace my datasource with your existing query. I can not test your query, but i try to fix it.
let Source = Excel.Workbook(File.Contents("C:\Users\Manuelm\Desktop\Project reporting\GPC\GPC -- Invoices and Payments.xlsx"), null, true), GPCInvPayStatus_Table = Source{[Item="GPCInvPayStatus",Kind="Table"]}[Data], #"Removed Columns" = Table.RemoveColumns(GPCInvPayStatus_Table,{".", "Fälligkeit", "Zahlung", "Fäl.", "Zahl.Art", "Zahlungsart"}) ProjectColumns = List.Select(Table.ColumnNames(#"Removed Columns"), each not Text.Contains(_, "amount")), AmountColumns = List.Select(Table.ColumnNames(#"Removed Columns"), each Text.Contains(_, "amount")), GetProjectColumns = List.Combine(List.Transform(ProjectColumns, each Table.Column(#"Removed Columns", _))), GetAmountColumns = List.Combine(List.Transform(AmountColumns, each Table.Column(#"Removed Columns", _))), CreateFinalTable = Table.FromColumns({GetProjectColumns,GetAmountColumns }, {"Project", "Project amount"}) in CreateFinalTable
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
At the step before you start unpivoting, rename the step FullTable.
First, select your key column and your P1-Px columns. Name this step PColumns.
Next, select your Key column, and unpivot other columns. Name this step Table1.
Add a new step with the formula, =FullTable. Name it FullTable2. Next, select your key column and your P amount columns. Name this PAmountColumns. Next, select your key column, and unpivot other columns. Name this Table2. Now, if you want just two columns, go back and rename your unpivoted columns the same name. Then Table.Combine(Table1, Table2). You could also Table.Join(Table1, {"KeyColumn"},Table2, {"KeyColumn"}, JoinKind.Inner)
The point is that each step is a table, and you can refer to them in any order.
