Forum Discussion

prashantmoger's avatar
prashantmoger
Frequent Visitor
4 years ago
Solved

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...
  • Vijay_A_Verma's avatar
    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"