Data table transformation

Hi Mederic 

It seems that you already get close to the desired outcome through the M code. Where do you want to make it dynamic? In earlier steps, you use some "not dynamic" code get the following column, 

so I update the code a bit to make it dynamic by using Merge queries feature to finally get the following Item Text column. See Data (2) query in the attachment. Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Hi Mederic, you almost had it, but check this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdZLb8IwDADg/8IZCdt5uefx2sYuPE6IwzRx4LJN1djvXxNopnTgkgWJQouUftiOTbfbgTKAg+FAAwIwNGfz5nhZ02KunhdIGzBgqPkKzAjUiICUv+gcqLRtPiabZfP+MJ6EW04/jvX74etY75uL39duGFAqRhVBYvpfsNx/vh7qS55qPe1Xjc/ebFmtTp71HooeO/MnyNW+/j68XYxQt2LlVz9GEYPoiF2fyM4loJJBUwxilmfv4KXbhsQSuuISElcZW4bPHob4VtGjk2dDttBmxCd71R28hNNi/RCKPa0Sz0jhIWZxuj88uf+Qir1OeHI7YJwwzq+dRc+e2sFUHEIQPGtMDqizwCsj7fb9ie18IfBr1x1PG/YeKcFzOuE8Lv5LYDtiMJBPXRLJF5nEnHLagj05dcWgCmm4uQm5GESbFlEcolgVe44zq0jtoAHrz6Zdk8K2JxZM0hmDm+IDjQlPCNFzwSNi1eehzelEiqPm/0m1nAOqW0CF0mzTaRWvze7dDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t, #"Supplier no." = _t, #"Supplier Name" = _t, #"ID Equipement" = _t, #"Repair Date" = _t, ColF = _t, #"Col G" = _t, #"Col H" = _t, #"Col I" = _t, #"Amount excl. Vat" = _t, Currency = _t, #"GL Code" = _t, Description = _t, #"Invoice Date" = _t, #"Invoice no." = _t, Other = _t, Statut = _t]),
    #"Colonne multipliée" = Table.TransformColumns(Source, {{"Amount excl. Vat", each Number.From(_) * 1.2, type number}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonne multipliée", {"GL Code"}, GL_Criteria, {"GL Cde"}, "GL_Criteria", JoinKind.LeftOuter),
    #"GL_Criteria développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "GL_Criteria", {"GL Account"}, {"GL Account"}),
    TransformedRepairDate = Table.TransformColumns(#"GL_Criteria développé", {{"Repair Date", each Date.Year(Date.From(_)), Int64.Type}}),
    #"Lignes groupées" = Table.Group(TransformedRepairDate, {"Repair Date", "GL Account"}, {{"Amount_Year", each List.Sum([Amount excl. Vat]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Lignes groupées", {"GL Account"}, GL_Grp_Criteria, {"GL Group"}, "GL_Grp_Criteria", JoinKind.LeftOuter),
    #"Expanded GL_Grp_Criteria" = Table.ExpandTableColumn(#"Merged Queries", "GL_Grp_Criteria", {"Item"}, {"Item"}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Expanded GL_Grp_Criteria", "Item Text", each "Adjusing " & Text.From([Repair Date]) & " to " & ReporDate & " " & [Item] , type text),
    #"Personnalisée ajoutée2" = Table.AddColumn(#"Personnalisée ajoutée1", "Date", each Date.From(ReporDate), type date),
    #"Personnalisée ajoutée3" = Table.AddColumn(#"Personnalisée ajoutée2", "Next Date", each Date.AddDays([Date],1), type date),
    #"Index ajouté" = Table.AddIndexColumn(#"Personnalisée ajoutée3", "Line no.", 1, 1, Int64.Type),
    #"Personnalisée ajoutée4" = Table.AddColumn(#"Index ajouté", "Company no.", each "FR340", type text),
    #"Personnalisée ajoutée5" = Table.AddColumn(#"Personnalisée ajoutée4", "Document Type", each "TS", type text),
    #"Personnalisée ajoutée6" = Table.AddColumn(#"Personnalisée ajoutée5", "Document Date", each Number.From(Date.ToText(Date.From([Date]), "yyyyMMdd")), Int64.Type),
    #"Personnalisée ajoutée7" = Table.AddColumn(#"Personnalisée ajoutée6", "Posting Date", each [Document Date], Int64.Type),
    #"Personnalisée ajoutée8" = Table.AddColumn(#"Personnalisée ajoutée7", "Document Reference", each "Adjusting " & Date.ToText(Date.From([Date]), "MM/yyyy"), type text),
    #"Personnalisée ajoutée9" = Table.AddColumn(#"Personnalisée ajoutée8", "Document Text", each [Document Reference], type text),
    #"Personnalisée ajoutée10" = Table.AddColumn(#"Personnalisée ajoutée9", "Cost Center", each "200T25G743", type text),
    #"Personnalisée ajoutée11" = Table.AddColumn(#"Personnalisée ajoutée10", "Location", each 25, Int64.Type),
    #"Personnalisée ajoutée12" = Table.AddColumn(#"Personnalisée ajoutée11", "Currency", each "EUR", type text),
    #"Personnalisée ajoutée13" = Table.AddColumn(#"Personnalisée ajoutée12", "Code VAT", each "DC", type text),
    #"Personnalisée ajoutée14" = Table.AddColumn(#"Personnalisée ajoutée13", "Calculate tax on brut", each null),
    #"Colonnes permutées" = Table.SelectColumns(#"Personnalisée ajoutée14",{"Line no.", "Company no.", "Document Type", "Document Date", "Posting Date", "Document Reference", "Document Text", "Currency", "GL Account", "Amount_Year", "Code VAT", "Item Text", "Cost Center", "Location", "Calculate tax on brut", "Next Date"})
in
    #"Colonnes permutées"

Hello Anonymous , dufoq3 ,

Thank you very much for your answers,
It helps me to correct my code..
However, the row for the total is missing.
And at the end, I'd like to sort the results according to 2 criteria
I'll explain all these points below :

1. 2. and 3. The sum total of rows 1 to 8 is missing
The GL Account will always have the account no. "23406400"
4. The text on the last row includes the word "Adjusting" & Years & Date reporting
5. The "Calculate Tax" column has a cross only on the last row
6. Sort the table by year and by GL Account

And above all, how can I have a step that groups together most of the steps, for example all the "Table.AddColumn" in a 3 or 4 steps ?
A much more elegant code than mine

Thanks in advance

Best regards

Hello everyone,
I've found another approach to try and get a code with fewer steps and a bit more elegant than my "Data_V1" version
I've called this new query "Data_V2".
However, I'm still not happy with the result and I'm starting to get seriously stuck 🙄😊.

Maybe there's another, more dynamic approach, and show me the end result

- Power BI File

Thanks in advance

Best regards