Need help

Hi there, I have below sample input data in my PowerBI data model. Based on this data, I want to create visualization as per below expected output data. basically a header which will have Fund na...

Need Help

DataNinja777
1 year ago
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:

Unpivot the original one-row table.

Create a column from examples to identify Current Quarter, Prior Quarter, and Difference.

Create a column from examples to identify Committed Capital, PIC, RES, and DIST.

Create separate columns to identify acct_num, acct_name, and Inception Date, then fill down.

Pivot the column field containing Committed Capital, PIC, RES, and DIST.

I have attached an example pbix file for your reference.
One-row table to 3-row table.pbix27 KB

DataNinja777

1 year ago

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,

Bansi008

Helper III

1 year ago

DataNinja777 i am facing one problem while re-structuring data as per above instructions. All the Prior Q-End columns for CommCap, PIC, Res etc is calculated using DAX query based on Current Q-End columns on the table view level and same is not visible in the transform view. To get this columns in the transform view, i need to calculate this column using custom column function. Can you please also help me with syntax for custom column which would work similar to below DAX query to calculate Prior Q-end columns.

DAX Query :-

Prior Q-End CommCap =

var PriorDate = tblOutput[Prior Q-End Date]

RETURN

CALCULATE(

MAX(tblOutput[Current Q-End CommCap]),

FILTER(

tblOutput,

tblOutput[Acct] = EARLIER(tblOutput[Acct]) &&

tblOutput[output_date] = PriorDate

)

DataNinja777

Super User

1 year ago

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,

Forum Discussion

Need help