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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Matrix Twist

I have a customer who uses a spreastsheet to track inventory.

It looks like this:

IDAttr1Attr2Attr3v1v2v1v2v1v2
1aba1x2y3z
1abb4x5y6z
1abc7x8x9z

 

As you can see it appears to be pivoted, but instead of being pivoted by the ID, it is pivoted by ID, and Attr 1-3. 

 

Now they are moving towards an online system that stores the data like this:

IDAttr1Attr2Attr3V1V2
1aba1x
1aba2y
1aba3z
1abb4x
1abb5y
1abb6z
1abc7x
1abc8y
1abc9z

 

But they still want the report to look like their spreadsheet.

 

Normally a Matrix would be just the thing but, a Matrix results in the mutiple anchors arranged hierarchacally, 

+ID                       V1   v2    v1    v2   v1 v2

  +Attr1

    + Attr3

         Attr3

Which they hate.

 

It occurred to me to do a multi column pivot in T-SQL and then just throw the result into a regular table, but T-SQL seems to insist that you know the the pivoted values, V1 and V2 ahead of time and essentially hard code them. In real life, this usually is not the case (and I never understood why they do that). Also, doing this in SQL is somethign I never really understood or mastered. 

 

So, I am looking for suggestions and alternative.

Wax Poetic!

 

Thanks.

 

2 REPLIES 2
VilmarSch
Post Patron
Post Patron

See if the attachment here helps

 

TptinDBoi4.gif

v-xinruzhu-msft
Community Support
Community Support

Hi @wsdcawefwqrevfq 

Depending on the information you provide, you can put it directly in a table visual, or you want to achieve something else, can you be more specific, from the information you give it is not difficult to understand what the final effect you want to look like.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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