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
bend1234567
Frequent Visitor

Table.Pivot - Pivoted Column - dealing with duplicates

I'm exporting data out of a jotform. The form is for daily inventory counts.

The counts are coming in specitic to warehouse Location and Bin (a location can have multiple bins). For example, item aaa is at location L1, and has 22 items in bin1 and and 10 items in bin2.

The form has a mini table in it, so the data exports out in a similar fashion as the below table (but has 8 sets of Location/Bin/Count columns instead of only 3).

DateUserItem NumLocation1Bin1Count1Location2Bin2Count2Location3Bin3Count3
3/1/2023U1aaaL1bin122L1bin210L2bin15
3/1/2023U1bbbL1bin12L1bin22L1bin32
3/1/2023U2cccL1bin13      

 

The summary I need to produce does not care about specific bins. I just need to sum the Counts by Item Num and Location.

 

I think im most of the way there but am getting stuck at the final step.

 

Summarized Steps I've taken:

1) clean the data

2) Unpivot the data, using a unique key, Date, User, and Item Num as my columns. This makes the table look something like the below table (in this example I'm only using the first two rows from the above table):

KeyDateUserItemAttributeValue
 3/1/2023U1aaaLocation1L1
 3/1/2023U1aaaBin1bin1
 3/1/2023U1aaaCount122
 3/1/2023U1aaaLocation2L1
 3/1/2023U1aaaBin2bin2
 3/1/2023U1aaaCount210
 3/1/2023U1aaaLocation3L2
 3/1/2023U1aaaBin3bin1
 3/1/2023U1aaaCount35
 3/1/2023U1bbbLocation1L1
 3/1/2023U1bbbBin1bin1
 3/1/2023U1bbbCount12
 3/1/2023U1bbbLocation2L1
 3/1/2023U1bbbBin2bin2
 3/1/2023U1bbbCount22
 3/1/2023U1bbbLocation3L1
 3/1/2023U1bbbBin3bin3
 3/1/2023U1bbbCount32

3) I dont care about which bin, so i filter out any rows where [Attribute] contains "Bin"

4) generalize the [Attribute] column into values containing either "Location" or "Count".

5) Unpivot, using the folowing:

= Table.Pivot(#"Step4", List.Distinct(#"Step4"[Attribute]), "Attribute", "Value", List.Sum)

 

This works for the summing of the Counts, but gives me errors on the Location column.

Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=L1
Right=L1

 

I think this has something to do with needing to aggregate duplicate Locations for the same Item.

If I change the Aggregate Value Function to 'dont aggregate' then I continue to get errors.

I am sure I can do this an a roundabout tedious way, but i would have thought that Table.Pivot can account for duplicates.

 

Any idea how to get around?

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

At step4) -    "4) generalize the [Attribute] column into values containing either "Location" or "Count".",

if you split the column from non-digit to digit and leave both columns in place, you can do the Pivot with "Don't aggregate".  There shouldn't be any errors at that point.

You can then sort out the datatypes, maybe remove columns and probably do a 'Group By' to get what you want

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

1) That's a yes. You get 2 columns Location/Count/Bin in one and values from 1-3 in the sample data

2) Yes.

--

What do you have after doing that?

Ok never mind, I was able to figure it out. I needed to Group By that Digit column, that was confusing me. But I got it to work now based on your response. Thanks for helping!

HotChilli
Super User
Super User

At step4) -    "4) generalize the [Attribute] column into values containing either "Location" or "Count".",

if you split the column from non-digit to digit and leave both columns in place, you can do the Pivot with "Don't aggregate".  There shouldn't be any errors at that point.

You can then sort out the datatypes, maybe remove columns and probably do a 'Group By' to get what you want

Hi, thanks for your response. Can you clarify a few things?

 

1) "if you split the column from non-digit to digit and leave both columns in place"

Does this mean end up with the same genralized Location or Count column, as well as a column for which set of columns (values being 1-8)?

 

2) "you can do the Pivot with "Don't aggregate""

Which column should I Pivot? The same Location/Count column?

 

I think i understand what you're getting at with the Group By, but I still cant get the Pivot to work

 

for anyone looking at this, I needed to Group By the digit column. That was confusing me but once i did that it worked for me.

wdx223_Daniel
Super User
Super User

= Table.Pivot(#"Step4", List.Distinct(#"Step4"[Attribute]), "Attribute", "Value", each try List.Sum(_) otherwise Text.Combine(_,","))

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.

Top Solution Authors
Top Kudoed Authors