Pivot Data into Lists without Aggregation/Counting or Summing the data

[Jelto83]

Hi,

I'm sure that this is going to be an easy solution for someone out there, but it's had me in circles the last few days.

Requirement: Users require a dynamic dependent dropdown list where they first select their supplier and then they can select only a SKU assigned to the supplier. Data is in PowerQuery table and is not sorted.

Problem: I have a solution already in excel however as the data is in a table which originates from PowerQuery, each time the query refreshes the data range in the table changes and the formaule doesn't grow/contract accordingly. I could use VBA but I'm sure there must be a way to get the below in PQ without excel heavy formulae and is dynamic.

Data Sample:

Desired Output (if possible):

Not to bothered if the Supplier ID is the header with SKU's in rows beneath OR if the Supplier ID is down the side with the SKU's going left to right in same row - this is how my current EXCEL solution is working as I can then offset the Data Validation List function accordingly.

I'm sure this is really easy to do but each time I try GROUP BY - Don't Aggregate or PIVOT the data it sums/counts the SKU values and not displays the SKU values.

Thanks in advance and hope the above is all clear!

Hannah

[Smauro]

Hi @Jelto83 

You could try this, as a new step:

.

let

    names = List.Distinct(Source[SupplierNumber])

in

    Table.FromColumns(Table.Transpose(Table.Pivot(Source, names, "SupplierNumber", "SKU", each _))[Column1], names)

Changing Source to your previous step's name.

[Jelto83 · Accepted solution]

I've found an iterim own solution to this. I had to add an INDEX Column before Pivoting the data with out Aggretating it and it worked. Now have to format the data around but in essence it has sort of worked.  Would still love a better solution in the longterm - I'm guessing some sort of incremental counter against each Supplier ID

[v-kelly-msft]

Hi @Jelto83 ,

Provide some sample data with expected output here,we would try to give some better solution on it.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

i think the table.group function is for you. If you submit a sample table, we can try it

[Anonymous]

you should do somethink like this:

let
.....
  your previuos steps
LastStep= somefunction(ofsomethonk)
.....
    #"Modificato tipo" = Table.TransformColumnTypes(LastStep,{{"sn", type text}, {"sku", type text}}),
    rr = Table.Group(#"Modificato tipo", {"sn"}, {{"skALL", each _[sku]}}),
    tfc=Table.FromColumns(rr[skALL],rr[sn])
in
tfc

[Jelto83]

No problem and thank you!

What is the best way to submit a sample table (sorry also new to this site!) 😁