Combine Line in powerquery

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.