Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

 

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.

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      ImkeF
      Community Champion
      7 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.