Forum Discussion

AlexParis's avatar
AlexParis
Frequent Visitor
6 years ago
Solved

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

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 o...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)))

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
AlexParis
Frequent Visitor
6 years ago

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