Forum Discussion
help with volume based on multiple columns
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, ...
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.
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.