Forum Discussion
Help with adjustment in table
Community ChampionYou're on the right track. Just comment out the first row which makes the query a function:
//(Table as table) =>
let
Fonte = Excel.Workbook(File.Contents("C:\Users\alexandrecr\Desktop\Cadastro Clientes Bi\Construção Bi\Cadastro de Clientes Detalhado.xlsx"), null, true),
Tabela1_Table = Fonte{[Item="Tabela1",Kind="Table"]}[Data],
#"TipoAlterado" = Table.TransformColumnTypes(Tabela1_Table,{{"Nº ", type text}, {"Cliente", type text}, {"CPF", type text}, {"RG", type text}, {"Data Nasc.", type date}, {"Celular", type text}, {"Tel.", type text}, {"Fax", type text}, {"Email", type text}, {"Sexo", type text}, {"Data Cad.", type date}, {"Empresa", type text}, {"Cod. Usuário", type text}, {"Usuário", type text}, {"Cód. Ag.", type text}, {"Ag.", type text}}),
#"Added Custom" = Table.AddColumn(TipoAlterado, "Custom", each Record.FieldValues(_)),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "EmptyRow", each List.IsEmpty(List.Select([Custom], (x) => x<> ""))),
#"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
Index3 = Table.AddIndexColumn(#"Added Index1", "Index.2", 2, 1),
#"Merged Queries" = Table.NestedJoin(Index3,{"Index.1"},Index3,{"Index"},"SplitRow",JoinKind.LeftOuter),
#"Merged Queries2" = Table.NestedJoin(#"Merged Queries",{"Index"},#"Merged Queries",{"Index.1"},"Merged Queries",JoinKind.LeftOuter),
#"Expanded Merged Queries" = Table.ExpandTableColumn(#"Merged Queries2", "Merged Queries", {"EmptyRow"}, {"Delete"}),
#"Expanded SplitRow" = Table.ExpandTableColumn(#"Expanded Merged Queries", "SplitRow", {"Custom"}, {"IsSplitRow"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded SplitRow",{"Index.2"},#"Expanded SplitRow",{"Index"},"Expanded SplitRow",JoinKind.LeftOuter),
#"Added Custom1" = Table.AddColumn(#"Merged Queries1", "AppendValues", each Table.SelectColumns([Expanded SplitRow], Tabela1_Table)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Delete] <> true) and ([EmptyRow] = false)),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Custom.1", each Table.FromColumns(List.Transform(List.Zip({[Custom], Record.FieldValues([AppendValues]{0})}), (x) => {Text.Combine(x, " ")}), Tabela1_Table)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Custom.1"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", Tabela1_Table)
in
#"Expanded Custom.1"Thanks, ImkeF
I got the following error:
Expression.Error: We cannot convert Table value type to type List.
Details:
Value=Table
Type=Type
- ImkeF
Cannot understand that that message, but it's probably due to the manipulation of the code that you've done.
So please go a different path instead:
Copy the first code I've given you into a new query: New query -> open the advanced editor and replace everything in there with the code I've given. This returns a function that has just one parameter: Your table.
In another query, just import your table that shall be manipulated.
Now, when you click on the query with my function code, you should see a window that allows you to select your table via the dropdown field.
Click "Invoke" and the results should appear.