Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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?

Anonymous
Not applicable

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! 👍

Anonymous
Not applicable

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors