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
peterg0417
Helper III
Helper III

Need to append 2 sets of data with different granularity - not sure how to approach this

I've been tasked with mind-numbing assignment and I'm not sure how to proceed. Everything I've learned in the past few years tells me that this approach is wrong, but the powers that be don't seem to think so.

 

I basically have to append 2 tables with the same basic structure, but 1 table gives me the "Value" column down to Asset Class granularity, while the other does not. I asked for granularity down to Asset Class, but the business unit may not be willing to provide it. 

 

Here's an example of what I'm dealing with, and "Value - Adjusted" is a suggested workaround:

peterg0417_0-1663336978780.png

 

Any suggestions on how to tackle this? I can't even figure out how to create "Value - Adjusted" in Power Query or DAX. I'm at the point where I feel like the only paths forward are requesting the data at the correct granularity, or saying this isn't possible.

 

Thank you in advance

1 ACCEPTED SOLUTION

Thanks for the file. See if this helps you get closer to what you need.

For the table with the bad structure, you can split into two tables:

a) a table which has the fields present in the good structure table but lacks the "Asset class" field: add a column for "asset class" with a a value of "undefined":

bad structure asset class.png

You can then append this table with the good struture table:

undefined asset.pngb) unpivot the columns with the "asset class" in the "bad structure" table to form a new table as follows:

single column .pngc) Create dimension tables for partner and asset class using the following:

Dim Asset Class =
DISTINCT (
    UNION (
        VALUES ( 'Single column'[Asset Class] ),
        VALUES ( 'Detailed columns'[asset class] )
    )
)

Dim Asset.png

Dim Partner =
DISTINCT (
    UNION (
        VALUES ( 'Detailed columns'[Partner] ),
        VALUES ( 'Single column'[Partner] )
    )
)

Dim partner.png

 d) create the relationships:

model.png

 

e) Build the matrix using the fields from the dimension table and SUM measures for eahc value to get:

matrix.png

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
PaulDBrown
Community Champion
Community Champion

What is the structure of both tables before you append them?

Can you post samples of each?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Yep. here's 2 screens since I can't post the files from the looks of it.

peterg0417_1-1663354554866.png

 

peterg0417_2-1663354611678.png

 

 

When I unpivot the table in the second pic, the values in the first 4 columns repeat for each line of asset class. We currently don't have that level of granularity. It's not provided by that division, BUT the business wants to see asset value, or equity as a whole, but also break it down by Partner or Asset Class (as long as those values are provided for each asset class).

I meant actual data; not images!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






what file types are supported? nethier xlsx or csv is allowed.

You can either copy and paste the data into a message or upload a file to a cloud service and share the link from there





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks for the file. See if this helps you get closer to what you need.

For the table with the bad structure, you can split into two tables:

a) a table which has the fields present in the good structure table but lacks the "Asset class" field: add a column for "asset class" with a a value of "undefined":

bad structure asset class.png

You can then append this table with the good struture table:

undefined asset.pngb) unpivot the columns with the "asset class" in the "bad structure" table to form a new table as follows:

single column .pngc) Create dimension tables for partner and asset class using the following:

Dim Asset Class =
DISTINCT (
    UNION (
        VALUES ( 'Single column'[Asset Class] ),
        VALUES ( 'Detailed columns'[asset class] )
    )
)

Dim Asset.png

Dim Partner =
DISTINCT (
    UNION (
        VALUES ( 'Detailed columns'[Partner] ),
        VALUES ( 'Single column'[Partner] )
    )
)

Dim partner.png

 d) create the relationships:

model.png

 

e) Build the matrix using the fields from the dimension table and SUM measures for eahc value to get:

matrix.png

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






This is amazing. Thank you! Over the weekend I started thinking of a similar solution, but you filled in some gaps for me with this solution. 

rsbin
Super User
Super User

@peterg0417 ,

Have an idea that may appease the Powers above.

For Partner 3, the current Value is approx 26% of the Total Invested (i.e. $5 / $19 M) or a 74% reduction.

Does it make sense to make an assumption that each Asset Class dropped by an equal amount?

Hence for Partner 3 - Industrial $4.9M * .26 = $1.27M.  Apply this to each Asset Class for this Partner and the Total then comes to $5M.

Let me know if this assumption can work...then we can proceed with next steps.

Regards,

I see where you're going with this, however it won't work. I had to change the numbers due to data confidentiality. The real numbers are different, and the Value column and Invested Amount columns have no relation. The original data structure is pivoted, so asset classes are their own columns going left to right. I unpivoted those columns to get the current structure since it makes more sense to work with the data like this in Power BI (where I can use Asset Class as a legend in a bar chart, for instance)

@peterg0417 ,

Thanks for the clarification.  Unpivoting is definitely the right step.

My next idea.  Can you create an Asset Class called "All"? 
For Partners 3 and 6 the values would be $5 and $6.5.  For the others it would be a sum of the Asset Classes.

When creating your visuals, you will have the flexibility of showing All for all Partners or the Class Level for those Partners that have them.

Hmm....I think that could work.

 

Would this be dynamic? Say next quarter Partner 1 has an invested amount in a new asset class, but the business unit still won't provide me with the proper granularity, will this option dynamically update too? Or am I hard coding Partner 3 and 6 to be 5$ and $6.5 for all assset classes?

 

Either way though, that idea just might work

@peterg0417 ,

Without knowing all your circumstances, I would approach this in the following way.

1. Create a Dimension table.  This would only have Partner ID, Partner Name and all other data elements associated with these entities.

2.  Keep your Fact tables separate.

2a) Create a Summary table for the ALL category.  So this table includes the Sum of All Asset Classes.

2b)  Keep your Detail table separate.  This table will not include Partner 3 or Partner 6

 

You can then join your Dimension table to these 2 fact tables.  Your data model is then set up to visualize as you see fit.

Lots of resources for how to create a Summary Table out there, but perhaps create a new thread if you think this direction works.

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.