Combine Line in powerquery

[Cheloo]

Dear All,

I use software that allows me to export accounting data as on (Table 1)

On the Label column of the operation, when the label is very long, during the data export, Excel automatically returns part of the label to the next line, which is not really to my taste, but I don't I have no power over this, however I would like to know if there is a manipulation in Excel using Power query that can allow this new line to be returned to the initial line as below like in (Table 2)

Initial and final

[Rickmaurinus · Accepted solution]

Hey Cheloo,

Here's an approach you can try. First I want to create a unique ID for each row. To do that: 

1. First filter away null values.
2. Then add an index column which will create the IDs
3. Then reference the table that includes null values (we do want these)
4. Perform a merge on the table without the nulls, so we can add the unique ID to the rows
5. Using the fill down operation, we can fill the ID and dates down to the rows that used to be empty
6. We can then use Table.Group in combination with Text.Combine for text and List.Sum for the values

You can copy paste the below script into the advanced editor to see all steps in action. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNboMwEIRfxeIcS14bG7gmaU79Uzn0EOXgkhVYTU1loBJ5+hpMqatGWtmalfab2T0eE844owwoh2TzK7gXTxVq638hhH99Kalk6is5bZY5AMrSn7lJSC8e2sH22ljyjPq9mwF8JQhQUqqZEFrlYO1I9noMcqo/+CLGT+K1Md0nOmNrsnWIV/Q94GmYlcrzQYGMHO5s1Wjb45kcWodd/8/IgxUFGYyCyLy410PdTDYv5gtdcFn2kFyAVAIilz126HpSanvubqzCcgr5emEvivlSrb2YnmxRV81ssPCLvMizCL5rryPZ6Tdjb4UXHriGFxTYFEfbasp+uOgP7GJ2nqUQBy977dxIHk3dxKc5fQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Valeur = _t, #"Libelle de l'operation" = _t, Debit = _t, credit = _t, solde = _t]),
    Mydata = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Valeur", type date}, {"Debit", Int64.Type}, {"solde", Int64.Type}, {"credit", Int64.Type}}), 
  RemoveNull         = Table.SelectRows (     Mydata,     each [Date] <> null ), 
  AddIndex           = Table.AddIndexColumn ( RemoveNull, "Index", 0, 1, Int64.Type ), 
  Show_Mydata_Step   = Mydata, 
  Merge_AddIndexStep = 
    Table.NestedJoin (
      Show_Mydata_Step, Table.ColumnNames ( Show_Mydata_Step ), 
      AddIndex,         Table.ColumnNames ( Show_Mydata_Step ), 
      "Custom1",        JoinKind.LeftOuter
  ), 
  ExpandIndex        = 
    Table.ExpandTableColumn (
      Merge_AddIndexStep, 
      "Custom1", 
      { "Index" }, 
      { "Index" }
    ), 
  FillDown           = Table.FillDown ( ExpandIndex, { "Date", "Valeur", "Index" } ), 
  GroupRows          = 
    Table.Group (
      FillDown, 
      { "Date", "Valeur", "Index" }, 
      {
        {
          "Libelle de l'operation", 
          each Text.Combine ( [#"Libelle de l'operation"], " " ), 
          type nullable text
        }, 
        { "Debit", each List.Sum ( [Debit] ),   type nullable number }, 
        { "credit", each List.Sum ( [credit] ), type nullable text }, 
        { "solde", each List.Sum ( [solde] ),   type nullable number }
      }
  )
in
  GroupRows

Let me know if this solves your issue, hope it helps!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[wdx223_Daniel]

NewStep=Table.Group(Table1,"Date",List.Transform(List.Skip(Table.ColumnNames(Table1)),(x)=>{x,each let a=Table.Column(_,x) in if a{0} is text then Text.Combine(a," ") else a{0}}),0,(x,y)=>Byte.From(y<>null))

[Cheloo]

Thanks so much