cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexParis
Frequent Visitor

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 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
 
Capture.jpg
1 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)))

View solution in original post

6 REPLIES 6
Rocco_sprmnt21
Community Champion
Community Champion

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.

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

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

 

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

 

 

the fifth element 🙂

 

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

 

Screenshot message error.jpg

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors