Combining pairs of items/units into one pair of columns

[masplin]

I've seen a few postes related ot this, but they deal with where for each row there is only one entry to for each item/unit combination.  in my case i have a single product and then 4 pairs of column with parts. The source file divides parts in fabric, hardware, trimming and other. Each column can have multiple items in it e.g 3 fabrics, 2 trimmings.  Like below.  Each sheet has multiple products on it.

I have tried ot use pivot and unpivot to basically stach al lthe items one on each row with a column of the units

30m Edging            1

40mm webbing      1

ITW D-ring 40mm  1

etc

I've atached the model so if anyone can crack this I would be grateful.

Unititem 

[Fowmy]

@masplin 

I could not follow your explanation to find out what type of result you are expecting. can you share the expected result? You can provide only for the 1st item.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[masplin]

Sorry try to be clearer. Top box is how I get the data and bottom box is how I want it

[v-kelly-msft · Accepted solution]

Hi @masplin ,

First go to query editor>Modify the 3 Unit column name as "Unit-item","Unit-item1","Unit-item2";

Then Select all 3 columns of items>Transform>Unpivot the columns,and you will see:

Then turn on the drop-down list of value column>remove empty;

Back to data view,create a calculated column as below:

Unit = SWITCH('Table'[Attribute],"Item",'Table'[Unit-item],
"Item.1",'Table'[Unit -item1],"Item.2",'Table'[Unit .1-item.2],BLANK())

Finally you will see:

For the related .pbix file,pls see attached.

Best Regards,

Kelly

If I have solved your issue,pls mark the reply as answered to close it!

[masplin]

That's  great. Is it possible to do the switch in power query so as not to import the extra columns? Guess i can just write an if statement

Thanks a lot

Mike

[Greg_Deckler]

@masplin Not quite clear. You can use & operator to concatenate things together in DAX. Maybe CONCATENATEX? What is the expected output?

[masplin]

I have 4 pairs of columns. Each pair is name of an item and then the quantity of that item. i want to stack them so I have one item column and one qty column each with the name of the prduct on the row. so if that product has 7 components I have 7 rows with 3 columns (product name, item, qty). I start with 6 rows of 9 columns (max 6 items in each category). if there were 2 items then there would be 2 rows of 3 columns. I seem tantilising close.

[Greg_Deckler]

@masplin Do you mean 4 pairs of rows? Because I do not see 4 pairs of columns in your data.

[masplin]

Can you see the image? It has item/unit/item/unit/item/unit/item/unit? I've added a row under the actual heading so the item/unit columns have same name as read a psot about this where i can then split of the _2,_3 that power query adds so when you unpivot it you have a column of alternate item/unit on each row.  This was a solution someone had when each pair of columns only had one entry, but it doenst work for multiple entries. 

if you open the model on the dropbox link that thats the actual data.

[Greg_Deckler]

@masplin Which sheet should I be using?

[masplin]

Sorry the sheets are just outputs of the query. if you edit the ancilliaries query thats the one