Forum Discussion
Combine multiple rows in one in bank statement (just for one field)
I import bank statements in M but my concern is that the field "Nature of the operation" is encountered on several rows.
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)))
6 Replies
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.
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
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?
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)))
