Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I've got a big project involving major transformations from a "Data" table to a new "Output" table.
I've prepared a pdf file with all the explanations
I've also put together an example file with the data
-Sample PowerBI ;
-Explanations in pdf
I am very grateful for your answers in advance
Thank you
Best regards
Solved! Go to Solution.
Done.
I've not included columns Document Text (this is the same as Invoice no.) and Currency (I don't know the currency from the source data)
= Table.Buffer(#"Type modifié")
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZVLbsIwEIavgrIOYmb8zLLi2ZZueKwQC0qzYAMoQO/Uc/RitQ1JSSiOwKhSggOK832M/3hms4gJwCiOOCCABnM1MOfbhIYD9jpEmoIAQeYnEC1gLQLi9kvlRMZjacbudGQ+250uKDP2NodsvdofstTecj6/x9xgjsmm8Z42ttlmme526Uc0j50SBSsxgpKQ1Rt9f20X2WK/2qxvNkpyI26f2zkZ9UfJ+GgkqfrICyMdK1FyYmYcp9nnamkrRHA2WSAjIVvulqtSCLlUYq+eCyl0Uoq0qpPSKlYep9/jRMRgIt7Eowfwysm8DMIFlAWvNemkBnrHavOTFrpajAstOmpJV1mUj62FeAD0lgWX/8xTwTzObuHpPFvKzu0XPHkMtEi0+wsenhSxdxspbWtP7aYm5nKsIAa4nq4kWIzpcuXr00V5NehoVoFyoe0NxDxQxSNv36kiRb5nokO+VJFI9uUm7wLoSrMrLwDDs7l2aAJrKknoe7kFBmuZ/qLv1OLeaAgZ7IayLhr3VS0JNlM6rkkQJX+auZ3xqpksGqW0lF7VjISdTtrXQ3hdD6lmWxbd0qW8W0CVgxJpVgdF6dtbLoB5pwyov/Sm9ow4/wE=", 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." = _t, Currency = _t, #"GL Code" = _t, Description = _t, #"Invoice Date" = _t, #"Invoice no." = _t, #"Corrected amount excl." = _t, Statut = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Supplier no.", Int64.Type}, {"Supplier Name", type text}, {"ID Equipement", type text}, {"Repair Date", type date}, {"ColF", Int64.Type}, {"Col G", Int64.Type}, {"Col H", Int64.Type}, {"Col I", Int64.Type}, {"Amount excl.", type number}, {"Currency", type text}, {"GL Code", type text}, {"Description", type text}, {"Invoice Date", type date}, {"Invoice no.", type text}, {"Corrected amount excl.", Int64.Type}, {"Statut", type text}}),
FilteredRows = Table.SelectRows(ChangedType, each ([Invoice Date] <> null)),
Ad_AmountHelper = Table.AddColumn(FilteredRows, "AmountHelper", each if [#"Corrected amount excl."] <> null then [#"Corrected amount excl."] else [#"Amount excl."], Currency.Type),
GroupedRows = Table.Group(Ad_AmountHelper, {"Supplier no.", "Invoice no."}, {{"All", each Table.AddColumn(_, "GL Account", (x)=> Criteria{[GL Code = x[GL Code]]}[GL Account]) , type table}, {"Amount incl. VAT", each -List.Sum([AmountHelper])*1.2, Currency.Type}}),
Ad_Transformed = Table.AddColumn(GroupedRows, "Transformed", each
[ a = Table.RowCount([All]),
b = Table.AddIndexColumn(Table.FromColumns({
List.Repeat({[#"Invoice no."]}, a+1), //Invoice no.
{[Amount incl. VAT]} & List.Transform([All][AmountHelper], (x)=> x * 1.2), //Amount incl. VAT
{null} & [All][GL Account], //GL Account
{[#"Supplier no."]}, //Supplier no.
{""} & List.Repeat({"DC"}, a), //Code VAT
{"X"} & List.Repeat({""}, a), //Calulate tax on brut
{"Repair acc. to Invoice " & [#"Invoice no."]} & List.Transform(List.Zip({ List.Transform([All][PO], (x)=> "Repair " & Text.From(x)), [All][GL Code] }), (y)=> Text.Combine(y, " - ")), //Item Text
{""} & List.Repeat({"200T25G743"}, a), //Cost Center
[ x = {Date.ToText([All][Invoice Date]{0}?, "ddmmyyyy")}, y = List.Repeat(x, a+1) ][y] //Document Date
}, type table[#"Invoice no."=text, Amount incl. VAT=Currency.Type, GL Account=Int64.Type, #"Supplier no."=Int64.Type, Code VAT=text, Calculate tax on brut=text, Item Text=text, Cost Center=text, Document Date=text]), "Line no.", 1, 1, Int64.Type)
][b], type table),
CombinedTransformed = Table.Combine(Ad_Transformed[Transformed]),
Ad_CompanyNo = Table.AddColumn(CombinedTransformed, "Company no.", each "FR340", type text),
Ad_DocumentType = Table.AddColumn(Ad_CompanyNo, "Document Type", each "TS", type text),
Ad_PostingDate = Table.AddColumn(Ad_DocumentType, "Posting Date", each Date.From(DateTime.FixedLocalNow()), type date)
in
Ad_PostingDate
A little help please for my last point ?
Thank you in advance
Best regards
Done.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZVLbsIwEIavgrIOYmb8zLLi2ZZueKwQC0qzYAMoQO/Uc/RitQ1JSSiOwKhSggOK832M/3hms4gJwCiOOCCABnM1MOfbhIYD9jpEmoIAQeYnEC1gLQLi9kvlRMZjacbudGQ+250uKDP2NodsvdofstTecj6/x9xgjsmm8Z42ttlmme526Uc0j50SBSsxgpKQ1Rt9f20X2WK/2qxvNkpyI26f2zkZ9UfJ+GgkqfrICyMdK1FyYmYcp9nnamkrRHA2WSAjIVvulqtSCLlUYq+eCyl0Uoq0qpPSKlYep9/jRMRgIt7Eowfwysm8DMIFlAWvNemkBnrHavOTFrpajAstOmpJV1mUj62FeAD0lgWX/8xTwTzObuHpPFvKzu0XPHkMtEi0+wsenhSxdxspbWtP7aYm5nKsIAa4nq4kWIzpcuXr00V5NehoVoFyoe0NxDxQxSNv36kiRb5nokO+VJFI9uUm7wLoSrMrLwDDs7l2aAJrKknoe7kFBmuZ/qLv1OLeaAgZ7IayLhr3VS0JNlM6rkkQJX+auZ3xqpksGqW0lF7VjISdTtrXQ3hdD6lmWxbd0qW8W0CVgxJpVgdF6dtbLoB5pwyov/Sm9ow4/wE=", 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." = _t, Currency = _t, #"GL Code" = _t, Description = _t, #"Invoice Date" = _t, #"Invoice no." = _t, #"Corrected amount excl." = _t, Statut = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Supplier no.", Int64.Type}, {"Supplier Name", type text}, {"ID Equipement", type text}, {"Repair Date", type date}, {"ColF", Int64.Type}, {"Col G", Int64.Type}, {"Col H", Int64.Type}, {"Col I", Int64.Type}, {"Amount excl.", type number}, {"Currency", type text}, {"GL Code", type text}, {"Description", type text}, {"Invoice Date", type date}, {"Invoice no.", type text}, {"Corrected amount excl.", Int64.Type}, {"Statut", type text}}),
FilteredRows = Table.SelectRows(ChangedType, each ([Invoice Date] <> null)),
MergedQueriesVAT = Table.NestedJoin(FilteredRows, {"Supplier no."}, VAT, {"Supplier no."}, "VAT", JoinKind.LeftOuter),
ExpandedVATCode = Table.ExpandTableColumn(MergedQueriesVAT, "VAT", {"VAT Code"}, {"VAT Code"}),
Ad_AmountHelper = Table.AddColumn(ExpandedVATCode, "AmountHelper", each if [#"Corrected amount excl."] <> null then [#"Corrected amount excl."] else [#"Amount excl."], Currency.Type),
GroupedRows = Table.Group(Ad_AmountHelper, {"Supplier no.", "Invoice no.", "VAT Code"}, {{"All", each Table.AddColumn(_, "GL Account", (x)=> Criteria{[GL Code = x[GL Code]]}[GL Account]) , type table}, {"Amount incl. VAT", each -List.Sum([AmountHelper])*1.2, Currency.Type}}),
Ad_Transformed = Table.AddColumn(GroupedRows, "Transformed", each
[ a = Table.RowCount([All]),
b = Table.AddIndexColumn(Table.FromColumns({
List.Repeat({[#"Invoice no."]}, a+1), //Invoice no.
{[Amount incl. VAT]} & List.Transform([All][AmountHelper], (x)=> x * 1.2), //Amount incl. VAT
{null} & [All][GL Account], //GL Account
{[#"Supplier no."]}, //Supplier no.
{""} & List.Repeat({[VAT Code]}, a), //Code VAT
{"X"} & List.Repeat({""}, a), //Calulate tax on brut
{"Repair acc. to Invoice " & [#"Invoice no."]} & List.Transform(List.Zip({ List.Transform([All][PO], (x)=> "Repair " & Text.From(x)), [All][GL Code] }), (y)=> Text.Combine(y, " - ")), //Item Text
{""} & List.Repeat({"200T25G743"}, a), //Cost Center
[ x = {Date.ToText([All][Invoice Date]{0}?, "ddmmyyyy")}, y = List.Repeat(x, a+1) ][y] //Document Date
}, type table[#"Invoice no."=text, Amount incl. VAT=Currency.Type, GL Account=Int64.Type, #"Supplier no."=Int64.Type, Code VAT=text, Calculate tax on brut=text, Item Text=text, Cost Center=text, Document Date=text]), "Line no.", 1, 1, Int64.Type)
][b], type table),
CombinedTransformed = Table.Combine(Ad_Transformed[Transformed]),
Ad_CompanyNo = Table.AddColumn(CombinedTransformed, "Company no.", each "FR340", type text),
Ad_DocumentType = Table.AddColumn(Ad_CompanyNo, "Document Type", each "TS", type text),
Ad_PostingDate = Table.AddColumn(Ad_DocumentType, "Posting Date", each Date.From(DateTime.FixedLocalNow()), type date)
in
Ad_PostingDate
Thank you very much @dufoq3,
I'm testing this tonight, but given your level it can only work.
Thank you for everything.
Best regardd
Hello everyone,
Hello @dufoq3 ,
The code works perfectly but there are 2 VAT codes depending on the supplier
How can I complete the M code to add this condition ?
Do you also know of any training courses/tutorials, paid or free, to progress in Power Query, preferably at an advanced level ?
Power BI File :
Pdf file for explanations :
Thank in advance
Best regards
@dufoq3 ,
It's impressive, it works perfectly in just a few steps,
The code is very well written and structured
Many thanks and have a great weekend
Best regards
Thank you very much @dufoq3 ,
I'm very grateful,
I'll take a look and get back to you tomorrow at the latest,
Good evening
Best regards
Hello,
Here are some other explanations that I hope will be clearer :
Power BI file :
Explanations .pdf
Thanks in advance
Best regards
but in Data there are 2 different invoices, you've considered only last row (second invoice). Why?
@dufoq3 ,
1. That's right, Lines without an invoice number should be ignored until the invoice has been received.
When the invoice arrives, I should add it to Data then automatically to the Output table
2. Sorry, I made a mistake, the value in "Output" should be 4010100 and not 4010070 on the line at -330.72
but in Data there are 2 different invoices, you've considered only last row (second invoice). Why?
This error is also related to the one above
Thanks
Best regards
Done.
I've not included columns Document Text (this is the same as Invoice no.) and Currency (I don't know the currency from the source data)
= Table.Buffer(#"Type modifié")
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZVLbsIwEIavgrIOYmb8zLLi2ZZueKwQC0qzYAMoQO/Uc/RitQ1JSSiOwKhSggOK832M/3hms4gJwCiOOCCABnM1MOfbhIYD9jpEmoIAQeYnEC1gLQLi9kvlRMZjacbudGQ+250uKDP2NodsvdofstTecj6/x9xgjsmm8Z42ttlmme526Uc0j50SBSsxgpKQ1Rt9f20X2WK/2qxvNkpyI26f2zkZ9UfJ+GgkqfrICyMdK1FyYmYcp9nnamkrRHA2WSAjIVvulqtSCLlUYq+eCyl0Uoq0qpPSKlYep9/jRMRgIt7Eowfwysm8DMIFlAWvNemkBnrHavOTFrpajAstOmpJV1mUj62FeAD0lgWX/8xTwTzObuHpPFvKzu0XPHkMtEi0+wsenhSxdxspbWtP7aYm5nKsIAa4nq4kWIzpcuXr00V5NehoVoFyoe0NxDxQxSNv36kiRb5nokO+VJFI9uUm7wLoSrMrLwDDs7l2aAJrKknoe7kFBmuZ/qLv1OLeaAgZ7IayLhr3VS0JNlM6rkkQJX+auZ3xqpksGqW0lF7VjISdTtrXQ3hdD6lmWxbd0qW8W0CVgxJpVgdF6dtbLoB5pwyov/Sm9ow4/wE=", 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." = _t, Currency = _t, #"GL Code" = _t, Description = _t, #"Invoice Date" = _t, #"Invoice no." = _t, #"Corrected amount excl." = _t, Statut = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Supplier no.", Int64.Type}, {"Supplier Name", type text}, {"ID Equipement", type text}, {"Repair Date", type date}, {"ColF", Int64.Type}, {"Col G", Int64.Type}, {"Col H", Int64.Type}, {"Col I", Int64.Type}, {"Amount excl.", type number}, {"Currency", type text}, {"GL Code", type text}, {"Description", type text}, {"Invoice Date", type date}, {"Invoice no.", type text}, {"Corrected amount excl.", Int64.Type}, {"Statut", type text}}),
FilteredRows = Table.SelectRows(ChangedType, each ([Invoice Date] <> null)),
Ad_AmountHelper = Table.AddColumn(FilteredRows, "AmountHelper", each if [#"Corrected amount excl."] <> null then [#"Corrected amount excl."] else [#"Amount excl."], Currency.Type),
GroupedRows = Table.Group(Ad_AmountHelper, {"Supplier no.", "Invoice no."}, {{"All", each Table.AddColumn(_, "GL Account", (x)=> Criteria{[GL Code = x[GL Code]]}[GL Account]) , type table}, {"Amount incl. VAT", each -List.Sum([AmountHelper])*1.2, Currency.Type}}),
Ad_Transformed = Table.AddColumn(GroupedRows, "Transformed", each
[ a = Table.RowCount([All]),
b = Table.AddIndexColumn(Table.FromColumns({
List.Repeat({[#"Invoice no."]}, a+1), //Invoice no.
{[Amount incl. VAT]} & List.Transform([All][AmountHelper], (x)=> x * 1.2), //Amount incl. VAT
{null} & [All][GL Account], //GL Account
{[#"Supplier no."]}, //Supplier no.
{""} & List.Repeat({"DC"}, a), //Code VAT
{"X"} & List.Repeat({""}, a), //Calulate tax on brut
{"Repair acc. to Invoice " & [#"Invoice no."]} & List.Transform(List.Zip({ List.Transform([All][PO], (x)=> "Repair " & Text.From(x)), [All][GL Code] }), (y)=> Text.Combine(y, " - ")), //Item Text
{""} & List.Repeat({"200T25G743"}, a), //Cost Center
[ x = {Date.ToText([All][Invoice Date]{0}?, "ddmmyyyy")}, y = List.Repeat(x, a+1) ][y] //Document Date
}, type table[#"Invoice no."=text, Amount incl. VAT=Currency.Type, GL Account=Int64.Type, #"Supplier no."=Int64.Type, Code VAT=text, Calculate tax on brut=text, Item Text=text, Cost Center=text, Document Date=text]), "Line no.", 1, 1, Int64.Type)
][b], type table),
CombinedTransformed = Table.Combine(Ad_Transformed[Transformed]),
Ad_CompanyNo = Table.AddColumn(CombinedTransformed, "Company no.", each "FR340", type text),
Ad_DocumentType = Table.AddColumn(Ad_CompanyNo, "Document Type", each "TS", type text),
Ad_PostingDate = Table.AddColumn(Ad_DocumentType, "Posting Date", each Date.From(DateTime.FixedLocalNow()), type date)
in
Ad_PostingDate
Hello @dufoq3
Thank you for your reply and sorry for the explanations. I had put some explanations in my Excel workbook but didn't think to put them in Power BI.
I will try to post a new Power BI file with all the explanations.
About the cost centre, it's the same and it will be opposite the positive amounts. I should have created this Text parameter in Power BI
For the texts, it's the same text according to the positive or negative amounts and adding the invoice number, PO number and the corresponding GL code.
Thanks in advance
Best regards