Data table transformation

[Mederic]

Hello everyone,

I need, please, your help in Power Query
This topic is another transformation model with an identical table to the topic I posted here.
I tried to take inspiration from dufoq3's excellent work for this topic but unfortunately I wasn't able to.
Nevertheless, I gave it a try in the Power BI file attached, but I'm a long way from the result

Especially as my code is not dynamic and also has too many steps.
If you could help me on this subject !

I've also included a .pdf file with explanations that I hope are clear.
I am grateful in advance for your support.

- .pdf File

- Power BI File

Best regards

[Mederic]

Hello everyone,
I have a 3rd approach with Data_V3 which almost satisfies me but I think we can do better.
I also have to sort by "Year" and by "GL Account", which I don't know how to do.

- Power BI File

Thank you in advance for any help or solutions

Best regards

[v-jingzhan-msft]

Hi @Mederic 

Great! Every step forward is appreciated. The sorting will be easy. You can insert a step for sorting by Repair Date and GL Account columns before the last step which gets the total row. Just like below. 

= Table.Sort(#"Lignes groupées",{{"Repair Date", Order.Ascending},{"GL Account", Order.Ascending}})

Best regards,

Jing

[Mederic]

Hello @v-jingzhan-msft ,

Thank you for your reply.

I thought I'd already tried this before and thanks, it works.
I must be tired 😊

Best regards

[Mederic]

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

[Mederic]

Hello @v-jingzhan-msft , @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

[dufoq3]

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"

[v-jingzhan-msft]

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!