Forum Discussion
prashantmoger
4 years agoFrequent Visitor
Unbilled aging
Hi, Wonder if anyone can help, iam trying to create Unbilled aging report in power querry, which will show from which month onwards the project is becoming unbilled for invoice. For Exampl...
- 4 years ago
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lEKKMrPSk0uUTAEsh0LijJzgLRSrA52Bb6JlUBS18QACHAq8irNSwVSxgZmZuZ4FOWAjLIwNsapxLE0vbS4hICi4NSCktTcpNQiINvIEJ9K/+SSfIg6fKr88stgxuFT5pKaTIwyvAFqBA9QnNLQoDQ1MzfDowYckiYGloY41cCDEq8q5LC0NMOjEBGUOJUghSNONUiBaGpkAQzFWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Project Name" = _t, Month = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Project Name", type text}, {"Month", type text}, {"Value", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Entity", "Project Name"}, {{"Temp", each _, type table [Entity=nullable text, Project Name=nullable text, Month=nullable text, Value=nullable number]}}), fxProcess = (Tbl)=> let //Source = Table, //#"India_Project 1" = Source{[Entity="India",#"Project Name"="Project 1"]}[Temp], #"Added Index" = Table.AddIndexColumn(Tbl, "Index1", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Value],[Index1]))), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] > 0){0} in #"Filtered Rows", #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])), #"Expanded fxProcess" = Table.ExpandRecordColumn(#"Invoked Custom Function", "fxProcess", {"Month", "Index"}, {"Month", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}), #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Columns", {"Index"}, "Removed Columns", JoinKind.LeftOuter), #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Month"}, {"Unbilled from month"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Removed Columns",{"Index"}) in #"Removed Columns1"
Vijay_A_Verma
4 years agoMost Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lEKKMrPSk0uUTAEsh0LijJzgLRSrA52Bb6JlUBS18QACHAq8irNSwVSxgZmZuZ4FOWAjLIwNsapxLE0vbS4hICi4NSCktTcpNQiINvIEJ9K/+SSfIg6fKr88stgxuFT5pKaTIwyvAFqBA9QnNLQoDQ1MzfDowYckiYGloY41cCDEq8q5LC0NMOjEBGUOJUghSNONUiBaGpkAQzFWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Project Name" = _t, Month = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Project Name", type text}, {"Month", type text}, {"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Entity", "Project Name"}, {{"Temp", each _, type table [Entity=nullable text, Project Name=nullable text, Month=nullable text, Value=nullable number]}}),
fxProcess = (Tbl)=>
let
//Source = Table,
//#"India_Project 1" = Source{[Entity="India",#"Project Name"="Project 1"]}[Temp],
#"Added Index" = Table.AddIndexColumn(Tbl, "Index1", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Value],[Index1]))),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] > 0){0}
in
#"Filtered Rows",
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
#"Expanded fxProcess" = Table.ExpandRecordColumn(#"Invoked Custom Function", "fxProcess", {"Month", "Index"}, {"Month", "Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Columns", {"Index"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Month"}, {"Unbilled from month"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Removed Columns",{"Index"})
in
#"Removed Columns1"prashantmoger
4 years agoFrequent Visitor
Thank you so much. Vijaya sir