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
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.
Solved! Go to 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:
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,
@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?
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:
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 :-
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
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,
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |