Need help

Hi Bansi008 ,

I confirm that your desired output is perfectly doable and a piece of cake using Power Query 😀.

I am assuming that you have multiple funds and that the one row in your example is just one of many. You can produce the output as shown below, which properly identifies the account number, account name, and inception date, even if you have multiple entries.

 

 

The steps I used to restructure the original one-row data into the above three-row data for each fund are:

1. Unpivot the original one-row table.
2. Create a column from examples to identify Current Quarter, Prior Quarter, and Difference.
3. Create a column from examples to identify Committed Capital, PIC, RES, and DIST.
4. Create separate columns to identify acct_num, acct_name, and Inception Date, then fill down.
5. Pivot the column field containing Committed Capital, PIC, RES, and DIST.

I have attached an example pbix file for your reference.  

 

Hi Bansi008 ,

 

Here is how you might construct the custom column using Power Query's M syntax:

let
    // Load your main table
    Source = tblOutput,

    // Create a reference to the main table
    tblOutput_Prior = Source,

    // Merge the main table with the reference table on the desired conditions
    MergedTables = Table.NestedJoin(
        Source,
        {"Acct", "output_date"},
        tblOutput_Prior,
        {"Acct", "Prior Q-End Date"},
        "MergedTable",
        JoinKind.LeftOuter
    ),

    // Expand the columns from the merged table
    ExpandedColumns = Table.ExpandTableColumn(
        MergedTables,
        "MergedTable",
        {"Current Q-End CommCap"},
        {"Prior Q-End CommCap"}
    )
in
    ExpandedColumns

Best regards,