Create script or sub-routine of recurrent steps

[DanFromMontreal]

Hello all,

I would like to know if it is possible to create a script / sub-routine of recurrent steps?

It looks like that multiple of my queries always start with the SAME steps.

So, in order to simplify my code, I would love to ceate a sub-routine like in VBA (Call xyz)... if possible.

Is it feasable?

Recurrent steps...

#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replace Cols Names" = Table.TransformColumnNames(#"En-têtes promus", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfter[Before],tblBeforeAfter[After]})), Comparer.OrdinalIgnoreCase))),
#"Table Combine" = Table.Combine({#"Replace Cols Names", tblAssurePrésenceDeCesColonnes}),
#"Lignes filtrées (_zzz)" = Table.SelectRows(#"Table Combine", each not Text.StartsWith([Numero_du_systeme_source], "_zz")),
#"Reorder columns" = Table.ReorderColumns(#"Lignes filtrées (_zzz)",List.Select(tblAlignementColonne[Alignement colonnes], (i)=> List.Contains(Table.ColumnNames(#"Lignes filtrées (_zzz)"),i))&List.Difference(Table.ColumnNames(#"Lignes filtrées (_zzz)"),tblAlignementColonne[Alignement colonnes])),
#"Type modifié" = Table.TransformColumnTypes(#"Reorder columns",tblDataType),
#"Lignes triées" = Table.Sort(#"Type modifié",{{"Numero_du_systeme_source", Order.Ascending}})

[Vijay_A_Verma · Accepted solution]

Equivalent is function in PQ. You can create a function and use that function in all queries.

https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions

https://docs.microsoft.com/en-us/power-query/custom-function

[DanFromMontreal]

@Vijay_A_Verma , I've spent the afternoon reading and watching videos on youtube on how to convert those simple step into a function but could not make it work unfortunately.

I'm close but I need some advice.

Lines in RED should be part of my fx so right after my Source line, it should be calling my fx (fxConversion)

Retuning from my fx, it completes the Query with a Table.Sort and possibly other transformations.

What is the M code to invoke that Customized Function that will take to Output of my previous step (Source), run it through the fx, transforming it and returning the Outpout to continue within the original Query????  That part, I don't get!

Thank you for the help

let
Source = Csv.Document(File.Contents(FullPathFilenameRS),[Delimiter=";", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replace Cols Names" = Table.TransformColumnNames(#"En-têtes promus", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfter[Before],tblBeforeAfter[After]})), Comparer.OrdinalIgnoreCase))),
#"Table Combine" = Table.Combine({#"Replace Cols Names", tblAssurePrésenceDeCesColonnes}),
#"Lignes filtrées (_zzz)" = Table.SelectRows(#"Table Combine", each not Text.StartsWith([Numero_du_systeme_source], "_zzz")),
#"Reorder columns" = Table.ReorderColumns(#"Lignes filtrées (_zzz)",List.Select(tblAlignementColonne[Alignement colonnes], (i)=> List.Contains(Table.ColumnNames(#"Lignes filtrées (_zzz)"),i))&List.Difference(Table.ColumnNames(#"Lignes filtrées (_zzz)"),tblAlignementColonne[Alignement colonnes])),
#"Type modifié" = Table.TransformColumnTypes(#"Reorder columns", tblDataType),
#"Lignes triées" = Table.Sort(#"Type modifié",{{"Numero_du_systeme_source", Order.Ascending}})
in
#"Lignes triées"

This is the Code for fxConversion

(fxConversion as table) =>
let
#"En-têtes promus" = Table.PromoteHeaders(fxConversion, [PromoteAllScalars=true]),
#"Replace Cols Names" = Table.TransformColumnNames(#"En-têtes promus", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfter[Before],tblBeforeAfter[After]})), Comparer.OrdinalIgnoreCase))),
#"Table Combine" = Table.Combine({#"Replace Cols Names", tblAssurePrésenceDeCesColonnes}),
#"Lignes filtrées (_zzz)" = Table.SelectRows(#"Table Combine", each not Text.StartsWith([Numero_du_systeme_source], "_zzz")),
#"Reorder columns" = Table.ReorderColumns(#"Lignes filtrées (_zzz)",List.Select(tblAlignementColonne[Alignement colonnes], (i)=> List.Contains(Table.ColumnNames(#"Lignes filtrées (_zzz)"),i))&List.Difference(Table.ColumnNames(#"Lignes filtrées (_zzz)"),tblAlignementColonne[Alignement colonnes])),
#"Type modifié" = Table.TransformColumnTypes(#"Reorder columns", tblDataType)
in
#"Type modifié"