Need help

[Bansi008]

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 name, Inception date and Fund number then a comparison table as per expected output data using input data values. Can someone please help me how can achive this in powerbi. detailed guidance would be helpful.

[DataNinja777 · Accepted solution]

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.  

[Bansi008]

@DataNinja777 thank you for your response. yes I know first row is not aligned with expected table. for now I can ignore first row which are merged cells in the excel (i created for example ) but can you please confirm if this table is doable using input data? and how?

[DataNinja777 · Accepted solution]

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.  

[Bansi008]

@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 · Accepted solution]

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]

@DataNinja777 thank you so much. I thought this is not possible in the PowerBI but looks like it is doable now. Let me try this in my real data and come back to you. 
thanks again

[DataNinja777]

Hi @Bansi008 ,

The first row of your desired output does not appear to be in a structured format, as the header texts are not aligned. Power BI handles structured data, and merged cells, like those in Excel, are not supported. Please let me know if your intention for the first row was not to create merged cells.

Best regards,