Forum Discussion
Mederic
2 years agoPost Patron
Data table transformation
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 ...
- 2 years ago
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.Thank you in advance for any help or solutions
Best regards
dufoq3
2 years agoCommunity Champion
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"