Forum Discussion
fsadiq
2 years agoFrequent Visitor
Split a value across 12 months/A year
Hi All, I have a table from a SharePoint List as shown below What i want is to split the Priority_Amount into 12 Months/1 Year as shown below by leveraging the year of any of the Urgent_da...
Syndicate_Admin
Administrator
2 years agolet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktKFFwVNJRMjUwMABShvqG+kYGRiYgplKsDlSBE5BrgqHACKHAGcg1xlBgjFDgAlKPocBEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Select = _t, Priority_Amount = _t, Urgent_Date = _t, ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Select", type text}, {"Priority_Amount", Currency.Type}, {"Urgent_Date", type date}, {"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..11}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Urgent_Date],each Date.AddMonths([Urgent_Date],[Custom]),Replacer.ReplaceValue,{"Urgent_Date"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Priority_Amount],each [Priority_Amount]/12,Replacer.ReplaceValue,{"Priority_Amount"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Priority_Amount", Currency.Type}, {"Urgent_Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Select", "Priority_Amount", "Urgent_Date", "ID"})
in
#"Removed Other Columns"fsadiq
2 years agoFrequent Visitor
Thanks Syndicate_Admin .
I tried running the query. Not sure if i applied the codes the right way but i created a blank query and pasted the codes but i rather got the below (Image)
Thank you once again.