Forum Discussion
Unpivot multile columns
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
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
CreateFinalTable
Copy 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
Jimmy
Hi 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
