Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 @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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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:
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks, @ImkeF
I got the following error:
Expression.Error: We cannot convert Table value type to type List.
Details:
Value=Table
Type=Type
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks a lot, @ImkeF, thats works!
But it also affects rows with no mistakes. There is a way to handle this?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I have no idea, but if it can be done, @ImkeF will know how to do it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |