help with volume based on multiple columns

[Anonymous]

Hello everyone

I have a CSV file that lists one order per row, however, each order could potentially be worked on by multiple vendors. The vendor columns are listed vendor 1, vendor 2, vendor 3, etc. 

I am attempting to find Vendor Volume--the number of orders each vendor has touched--but am having difficulty doing this. I have figured out a way, but it is very complicated, and due to this, is eating up a lot of memory and takes an hour to load.

I would like to pick your brains to see if there is an easier way to do this. It seems there should be, I am just not grasping it, and of course I apparently like to make things harder for myself than they need to be. 🙂 

Here is some sample data:

Order #	Vendor 1	Vendor 1 Result	Vendor 2	Vendor 2 Result	Vendor 3	Vendor 3 Result
123	Bob	Review	Joe	Review	Judy	Clear
124	Judy	Clear
125	Joe	Review	Bob	Clear
126	Joe	Review	Judy	Clear

There is additional data of course, such as Time Order Submitted, Time Order Complete, Time Submitted to Vendor 1, Time Vendor 1 Completed, etc. but those are not pertinent for this inquiry.

Using the above example, I would like to identify the volume per vendor, as follows:

Bob: 2

Joe: 3

Judy: 3

I am unable to share my actual data set with you as it is proprietary. Some things to keep in mind: we have upwards of 35 vendors, up to 10 vendors can potentially work on the same order, and the CSV containing this data currently has 800k rows though thousands will be added weekly.

Please help! Thank you so much in advance!

[v-yuaj-msft]

Hi @Anonymous ,

@parry2k 's idea is great. Or you can try to add a custom M query in advanced editor as follows:

#"merge"=Table.Combine({
Table.RenameColumns(Table.SelectColumns(#"Changed Type",{"Order #","Vendor 1","Vendor 1 Result"}),{{"Vendor 1","Vendor"},{"Vendor 1 Result","Vendor Result"}}),
Table.RenameColumns(Table.SelectColumns(#"Changed Type",{"Order #","Vendor 2","Vendor 2 Result"}),{{"Vendor 2","Vendor"},{"Vendor 2 Result","Vendor Result"}}),
Table.RenameColumns(Table.SelectColumns(#"Changed Type",{"Order #","Vendor 3","Vendor 3 Result"}),{{"Vendor 3","Vendor"},{"Vendor 3 Result","Vendor Result"}})
})
in
#"Removed Top Rows"

 then sort the "Vendor" column and remove blank rows.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[parry2k]

@Anonymous try the last approach I sent. Reason I asked what is your data source, not for you to send it to me but to see if it is a SQL Server or something like that and we can send this pivoting to the backend.

Since it is CSV, so data prep has to happen in PQ. Anyhow, go with my most recent approach, splitting vendor and result into two different tables and then use the relationship to visualize the data. Try that first and we can go from there.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

This works perfectly! Ahhhh thank you so much, this is incredible. I can actually load now. Thank you @parry2k !

[parry2k · Accepted solution]

@Anonymous here is another approach where we split vendor and result in two different tables to avoid pivoting and then with the relationship we can achieve the result.

[parry2k]

@Anonymous ok then pivot is going to be painful. what is your data source?

[Anonymous]

@parry2k It is a folder with 4 of the same types of CSV files combined (just different time periods). Unfortunately I cannot share it as it is proprietary. The CSV files come from a source provider for ours so we have no control over them but they are nastily built. Yes, I do not think pivoting them back is going to happen, my load seems to be stuck now. Any other ideas? You've been so helpful so far!

[parry2k]

@Anonymous see vendor 2 table, this is what I will do if no result column required.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thanks @parry2k , I do need all the other columns on that row: each vendor has their own time submitted, time completed, result and fee, and so I need those to all be inlcuded as well. It's quite hefty! I'm still loading too. 😕

[parry2k]

@Anonymous well unpivot should be quick but pivoting is slow, I do pivot it back to get the vendor and the other column on the same row, if you don't need that, just simply vendor then you can remove those extra steps after unpivot and it should be quicker. so not sure if you need that or not

[parry2k]

@Anonymous solution attached, with this transformation you can achieve and filter on anything, I gave few visuals on how you can have a scalable solution. It all boils down to the data model and how to optimize your tables.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Ahhh, unpivot! Thank you, I had absolutely forgotten about that!! I am testing it now to see if it works for me as well as it seems like it will; however, it's been applying my query changes for over an hour now so I am a little concerned. If it goes through, I will come and mark yours as a solution. Thank you!

[parry2k]

@Anonymous The better design and approach here is to unpivot your data and then you don't need to do anything as everything will line up nicely. You can unpivot easily in PQ.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡