Update Category

[Rajkumar21]

I need an m code for the below scenario:
I have got a result sheet which contains 10 lakhs row till X columns.. I'm focused on categories namely Ceased, Other movement, New, Price change, end date change. I manually create a pivot and filter for either one of the mentioned Categories and in the row I have assignments allocated to category. Then I check the value for each assignment if Amount in local currency column is not 0 then I pick the assignment go to the result sheet and filter for assignment number along with the category I choose in pivot and along with it blank.. then I check the sum of Amount in local currency. If it totals up to 0 or 1 then I update the blank as the category I selected in pivot. Similarly I do these process for all the other category. In these steps I don't get any of duplicasy. I need a similar steps but a optimised one in m code.

I have below code.. from InitializedData its not working.. please help..

let

// Load data

Source = Excel.CurrentWorkbook(){[Name="Result"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assignment", type text}, {"Amount in Local Currency", Currency.Type}, {"Document Date", type date}, {"Posting Date", type date}, {"Entry Date", type date}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Assign-Category", each [Assignment] & "-" & [Category]),



// Filter for relevant categories

FilteredCategories = Table.SelectRows(#"Added Custom", each List.Contains({"", "Ceased", "Other movement", "End date change"}, [Category])),



// Group by Assignment and Category, summing Amount

GroupedData = Table.Group(FilteredCategories, {"Assignment", "Category"}, {{"TotalAmount", each List.Sum([Amount in Local Currency]), type number}}),

#"Changed Type1" = Table.TransformColumnTypes(GroupedData,{{"TotalAmount", Currency.Type}}),



// Remove assignments with zero amount

NonZeroAssignments = Table.SelectRows(#"Changed Type1", each [TotalAmount] <> 0),

#"Added Custom1" = Table.AddColumn(NonZeroAssignments, "Assign-Category", each Text.Combine({[Assignment],[Category]},"-")),



// Reference the Added Custom step

#"SourceStep" = #"Added Custom",



// Merge with result sheet to get corresponding blank entries

MergedData = Table.NestedJoin(#"SourceStep", "Assign-Category", #"Added Custom1", "Assign-Category", "JoinedData", JoinKind.LeftOuter),

#"Expanded JoinedData1" = Table.ExpandTableColumn(MergedData, "JoinedData", {"TotalAmount"}, {"JoinedData.TotalAmount"}),



// Initialize New_Category column

InitializedData = Table.AddColumn(#"Expanded JoinedData1", "New_Category", each if [Category] = null then "" else [Category]),



// Update New_Category based on conditions

UpdatedData = Table.AddColumn(InitializedData, "New_Category", each

if [Category] = "" and ([JoinedData.TotalAmount] = 0 or [JoinedData.TotalAmount] = 1) then

if [JoinedData.TotalAmount] = 0 or [JoinedData.TotalAmount] = 1 then

if List.Contains({"Ceased", "Other movement", "End date change"}, [Category]) then [Category]

else if [Category] = "" then "Ceased"

else if [Category] = "" then "Other movement"

else if [Category] = "" then "End date change"

else [Category]

else [Category])

in

UpdatedData

 

[p45cal]

Could you attach an Excel workbook with a sample of the Result table. Not all the rows, but sufficient rows to be able to demonstrate the effect of the various transformations you'd like to apply in the M-Code; things like the grouping, filtering, concatenating and which will allow all possibilities in the UpdatedData step to be tested?