Help with adjustment in table

[Anonymous]

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.

I must concatenate rows everytime a blank row appears. If row 58 is blank, I need to concatenate rows 57 and 59. How can I do this in M? Is that possible? Please help, I have no idea how can I do this.

Thanks, and merry christmas.

[v-shex-msft]

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

[ImkeF]

Agree with @v-shex-msft

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.

[Anonymous]

Fix this report in our application is not an option in this moment, unfortunally... 

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.

[Anonymous]

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:

[ImkeF]

You'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"

[Anonymous]

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.

[Anonymous]

Thanks a lot, @ImkeF, thats works!

But it also affects rows with no mistakes. There is a way to handle this?

[ImkeF]

Sorry, but I don't understand the problem. Can you describe it more specific please? Maybe give a sample?

[Greg_Deckler]

I have no idea, but if it can be done, @ImkeF will know how to do it.