Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Bansi008
Helper II
Helper II

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 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.

Bansi008_0-1725017516683.png

 

2 ACCEPTED SOLUTIONS

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.

 

DataNinja777_1-1725070741926.png

 

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.  

 

View solution in original post

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,

View solution in original post

6 REPLIES 6
Bansi008
Helper II
Helper II

@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?

Bansi008_0-1725029056685.png

 

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.

 

DataNinja777_1-1725070741926.png

 

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.  

 

@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
    )
)

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,

@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
Super User
Super User

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,

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.