Combine multiple rows in one in bank statement (just for one field)

[AlexParis]

Hi All,

I import bank statements in M but my concern is that the field "Nature of the operation" is encountered on several rows.

How to make the text of the column "Nature of the operation" put on a single row please and not two or more? 

For example with the attached screenshot I would like row n°2 to disappear and in the column "Nature of operation" of row n°1 in the field to be written: 

CARD X8951 29/04 AMAZON EU SARL COMMERCE ELECTRONIQUE

Thank you very much for your help. Its' very appreciate.

Alex

[Anonymous]

give atry using these and let we know:

...

    #"Added Custom" = Table.AddColumn(#"Changed Type", "dat", each if Value.Is([date], type date)  then "#" else [date]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"dat"}, {{"nop", each  Text.Combine(_[noo], " ")},{"amnt", each List.First(_[amount])}},GroupKind.Local,(x,y)=> Number.From(x=y))
in
    #"Grouped Rows"

I called "noo" the column "Nature de l'operation"  and "amount" the column "debit".

you have to change one or the other to make all the same.

[AlexParis]

Thanks and sorry I don't understand what to change in the code M.

My fields are: Source.Name, Index, Date, Nature de l'opération, Débit, Crédit, Devise, Date de valeur, Libellé interbancaire.

Can you give more details please?

[Anonymous · Accepted solution]

I made the necessary change. Biut you have to change the name of tab with the parameter of your last step.

Use this after you last step

Table.Group(name_tab_previuo_step, {"Date"}, {{"nop", each Text.Combine(_[#"Nature de l'opération"]," ")},{"Debito", each List.First(_[Débit])}},GroupKind.Local,(x,y)=>Number.From((x=y or y[Date]<>null)))

[AlexParis]

It's more than perfect! Thank you soo much! 👍

[Anonymous]

the technique used here, with specific adaptations, is illustrated in the following blog

the fifth element 🙂

[AlexParis]

Here is the M code:

let
Source = Folder.Files("C:\Users\Alex\Desktop\Relevés bancaires"),
#"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
#"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
#"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
#"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
#"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"En-têtes promus" = Table.PromoteHeaders(#"Type modifié", [PromoteAllScalars=true]),
#"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{"Running - 052020.csv", "Source.Name"}}),
#"Type modifié1" = Table.TransformColumnTypes(#"Colonnes renommées",{{"Date", type date}}),
#"Erreurs supprimées" = Table.RemoveRowsWithErrors(#"Type modifié1", {"Date"}),
#"Type modifié2" = Table.TransformColumnTypes(#"Erreurs supprimées",{{"Débit", Currency.Type}, {"Crédit", Currency.Type}, {"Date de valeur", type date}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié2", each ([#"Nature de l'opération"] <> "")),
#"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées", "Index", 1, 1),
#"Colonnes permutées" = Table.ReorderColumns(#"Index ajouté",{"Source.Name", "Index", "Date", "Nature de l'opération", "Débit", "Crédit", "Devise", "Date de valeur", "Libellé interbancaire"}),
#"Type modifié3" = Table.TransformColumnTypes(#"Colonnes permutées",{{"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Type modifié3", "dat", each if Value.Is([date], type date) then "#" else [date]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"dat"}, {{"nop", each Text.Combine(_[noo], " ")},{"amnt", each List.First(_[amount])}},GroupKind.Local,(x,y)=> Number.From(x=y))
in
#"Grouped Rows"

I have this message (screenshot attached red line): "We did not find the recording "date" field".

Many thanks