Table.Pivot - Pivoted Column - dealing with duplicates

[Anonymous]

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

Date	User	Item Num	Location1	Bin1	Count1	Location2	Bin2	Count2	Location3	Bin3	Count3
3/1/2023	U1	aaa	L1	bin1	22	L1	bin2	10	L2	bin1	5
3/1/2023	U1	bbb	L1	bin1	2	L1	bin2	2	L1	bin3	2
3/1/2023	U2	ccc	L1	bin1	3

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

Key	Date	User	Item	Attribute	Value
	3/1/2023	U1	aaa	Location1	L1
	3/1/2023	U1	aaa	Bin1	bin1
	3/1/2023	U1	aaa	Count1	22
	3/1/2023	U1	aaa	Location2	L1
	3/1/2023	U1	aaa	Bin2	bin2
	3/1/2023	U1	aaa	Count2	10
	3/1/2023	U1	aaa	Location3	L2
	3/1/2023	U1	aaa	Bin3	bin1
	3/1/2023	U1	aaa	Count3	5
	3/1/2023	U1	bbb	Location1	L1
	3/1/2023	U1	bbb	Bin1	bin1
	3/1/2023	U1	bbb	Count1	2
	3/1/2023	U1	bbb	Location2	L1
	3/1/2023	U1	bbb	Bin2	bin2
	3/1/2023	U1	bbb	Count2	2
	3/1/2023	U1	bbb	Location3	L1
	3/1/2023	U1	bbb	Bin3	bin3
	3/1/2023	U1	bbb	Count3	2

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?

[HotChilli]

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?

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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]

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