Forum Discussion
Help with adjustment in table
Hi, I have a table like this: Our app is generating a table that contains, in some rows, this mistake above. The data is broken between 2 rows and there is a blank row in the middle. ...
HI Anonymous,
I think it should more simple to fix them on application side.
It is hard to achieve your requirement on power query side, you need to check all sample data to confirm the rule to coding formulas.
BTW, complex M query is poor performance and will cause the memory issue when you apply looping on it.
Regards,
Xiaoxin Sheng
Agree with Anonymous
This is an ugly transformation and it might be slow. But anyhow - if you need it, please try it out:
(Table as table) => let // Debug parameter // Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpSitWJVgIywHQykJECZlUAWZVgViqQlYaiKg0iEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), Source = Table, ColNames = List.Buffer(Table.ColumnNames(Source)), ChgType = Table.TransformColumnTypes(Source,List.Transform(ColNames, each {_, type text})), #"Added Custom" = Table.AddColumn(ChgType, "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], ColNames)), #"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, " ")}), ColNames)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Custom.1"}), #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", ColNames) in #"Expanded Custom.1"It's a function that you just have to feed in your source-table.
Fix this report in our application is not an option in this moment, unfortunally... :smileysad:
So much thanks, ImkeF!
But I am not sure how to insert this in Advanced Editor, how can I do this? Sorry, I am very beginner in M.
My table is called Cadastro.
I am trying something like this:
(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"But I get this error:
