Transpose or Unpivot?

[Trebor8484]

Hi, hoping someone may be able to help me.

I have a dataset that has 5 columns of items and quantities i.e. item 1, quantity 1, item 2, quantity 2, item 3, quantity 3 etc.

I have tried various ways to transpose columns to to rows and also Unpivot within Power Query but I am unable to get it right.

Original table:

What I am looking for:

Thanks

 

[mart37]

Maybe this:
Group: item 1 - item 5
Unpivot: group item1 - item 5
Expandtable
Group: item.quantity 1 - item.quantity 5
Unpivot: group item.quantity 1 - item.quantity 5
Expandtable
Table.SpiltColumn quantity.mark on space (mark--> sorry don't know the correct transalation
Table.SpiltColumn mark on space
AddColumn [quantity.mark.2]=[mark.2]
SelectRows (filter) =True
Remove columns

 

[Trebor8484]

Hi, didn't have much luck with this. When I group the quantity it sums the data. Assuming I must be doing something wrong.

 

[mart37]

By GROUP ON select by EDIT for ALL ROWS

 

[mart37]

This is the M_code (in Dutch)

let
    Bron = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"date", type datetime}, {"contractor", type text}, {"item 1", type text}, {"quantity 1", Int64.Type}, {"item 2", type text}, {"quantity 2", Int64.Type}, {"item 3", type text}, {"quantity 3", Int64.Type}, {"item 4", type text}, {"quantity 4", Int64.Type}, {"job ref unique", Int64.Type}}),
    #"Rijen gegroepeerd" = Table.Group(#"Type gewijzigd", {"item 1", "item 2", "item 3", "item 4", "item 5"}, {{"Item", each _, type table [date=nullable datetime, contract=text, job ref=number, mode=text, contractor=nullable text, acc no=number, case ref=number, incident ref=number, item 1=nullable text, quantity 1=nullable number, item 2=nullable text, quantity 2=nullable number, item 3=nullable text, quantity 3=nullable number, item 4=nullable text, quantity 4=nullable number, item 5=nullable text, quantity 5=nullable number, payment ref=text, job ref unique=nullable number, type=text]}}),
    #"Draaitabel is alleen voor de geselecteerde kolommen opgeheven" = Table.Unpivot(#"Rijen gegroepeerd", {"item 1", "item 2", "item 3", "item 4", "item 5"}, "Kenmerk", "Waarde"),
    #"Item uitgevouwen" = Table.ExpandTableColumn(#"Draaitabel is alleen voor de geselecteerde kolommen opgeheven", "Item", {"date", "contract", "job ref", "mode", "contractor", "acc no", "case ref", "incident ref", "item 1", "quantity 1", "item 2", "quantity 2", "item 3", "quantity 3", "item 4", "quantity 4", "item 5", "quantity 5", "payment ref", "job ref unique", "type"}, {"Item.date", "Item.contract", "Item.job ref", "Item.mode", "Item.contractor", "Item.acc no", "Item.case ref", "Item.incident ref", "Item.item 1", "Item.quantity 1", "Item.item 2", "Item.quantity 2", "Item.item 3", "Item.quantity 3", "Item.item 4", "Item.quantity 4", "Item.item 5", "Item.quantity 5", "Item.payment ref", "Item.job ref unique", "Item.type"}),
    #"Rijen gegroepeerd1" = Table.Group(#"Item uitgevouwen", {"Item.quantity 1", "Item.quantity 2", "Item.quantity 3", "Item.quantity 4", "Item.quantity 5"}, {{"quantity", each _, type table [Item.date=nullable datetime, Item.contract=nullable text, Item.job ref=nullable number, Item.mode=nullable text, Item.contractor=nullable text, Item.acc no=nullable number, Item.case ref=nullable number, Item.incident ref=nullable number, Item.item 1=nullable text, Item.quantity 1=nullable number, Item.item 2=nullable text, Item.quantity 2=nullable number, Item.item 3=nullable text, Item.quantity 3=nullable number, Item.item 4=nullable text, Item.quantity 4=nullable number, Item.item 5=nullable text, Item.quantity 5=nullable number, Item.payment ref=nullable text, Item.job ref unique=nullable number, Item.type=nullable text, Kenmerk=text, Waarde=text]}}),
    #"Draaitabel is alleen voor de geselecteerde kolommen opgeheven1" = Table.Unpivot(#"Rijen gegroepeerd1", {"Item.quantity 1", "Item.quantity 2", "Item.quantity 3", "Item.quantity 4", "Item.quantity 5"}, "Kenmerk", "Waarde"),
    #"quantity uitgevouwen" = Table.ExpandTableColumn(#"Draaitabel is alleen voor de geselecteerde kolommen opgeheven1", "quantity", {"Item.date", "Item.contract", "Item.job ref", "Item.mode", "Item.contractor", "Item.acc no", "Item.case ref", "Item.incident ref", "Item.item 1", "Item.quantity 1", "Item.item 2", "Item.quantity 2", "Item.item 3", "Item.quantity 3", "Item.item 4", "Item.quantity 4", "Item.item 5", "Item.quantity 5", "Item.payment ref", "Item.job ref unique", "Item.type", "Kenmerk", "Waarde"}, {"quantity.Item.date", "quantity.Item.contract", "quantity.Item.job ref", "quantity.Item.mode", "quantity.Item.contractor", "quantity.Item.acc no", "quantity.Item.case ref", "quantity.Item.incident ref", "quantity.Item.item 1", "quantity.Item.quantity 1", "quantity.Item.item 2", "quantity.Item.quantity 2", "quantity.Item.item 3", "quantity.Item.quantity 3", "quantity.Item.item 4", "quantity.Item.quantity 4", "quantity.Item.item 5", "quantity.Item.quantity 5", "quantity.Item.payment ref", "quantity.Item.job ref unique", "quantity.Item.type", "quantity.Kenmerk", "quantity.Waarde"}),
    #"Kolom splitsen op scheidingsteken" = Table.SplitColumn(#"quantity uitgevouwen", "quantity.Kenmerk", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"quantity.Kenmerk.1", "quantity.Kenmerk.2"}),
    #"Kolom splitsen op scheidingsteken1" = Table.SplitColumn(#"Kolom splitsen op scheidingsteken", "Kenmerk", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Kenmerk.1", "Kenmerk.2"}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Kolom splitsen op scheidingsteken1", "Aangepast", each [quantity.Kenmerk.2]=[Kenmerk.2]),
    #"Rijen gefilterd1" = Table.SelectRows(#"Aangepaste kolom toegevoegd", each ([Aangepast] = true)),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Rijen gefilterd1",{"quantity.Item.item 1", "quantity.Item.quantity 1", "quantity.Item.item 2", "quantity.Item.quantity 2", "quantity.Item.item 3", "quantity.Item.quantity 3", "quantity.Item.item 4", "quantity.Item.quantity 4", "quantity.Item.item 5", "quantity.Item.quantity 5", "quantity.Kenmerk.1", "quantity.Kenmerk.2", "Kenmerk.1", "Kenmerk.2", "Aangepast"})
in
    #"Kolommen verwijderd"