none
Grouping warehouse table with n records to single row RRS feed

  • Question

  • Dear community,

    I have following table with different warehouse records.
    See Image Before grouping

    and I would like to Group the warehouses "AA" and "WI" to a new field

    See Image after grouping

    Thanks a lot in advance

    Best Regards

    Andreas

        

    Friday, February 9, 2018 6:22 AM

Answers

All replies

  • Hi Andreas,

    please try posting your images again, as they don't appear in your post.

    Thx.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 9, 2018 9:17 PM
    Moderator
  • Hi Imke,

    thanks a lot for your reply.

    Unfortunately I received following message

    "Body text cannot contain Images or links until we are able to verify your accoung"

    Am I doing something wrong?

    Thanks a lot for your help.

    Have a nice day.

    Best Regards

    Andreas

    Monday, February 12, 2018 7:53 AM
  • Hi Andreas,

    you just have to be patient and wait some hours, then it should be possible :-)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, February 12, 2018 8:42 AM
    Moderator
  • Dear Imke,

    I'm still not able to insert Images into the message.

    Just for Information, I will be Patient.

    Have a nice day.

    Best Regards

    Andreas

    Wednesday, February 14, 2018 8:19 AM
  • Hi Andreas. Would you mind trying again?

    Ehren

    Wednesday, February 21, 2018 7:34 PM
    Owner
  • Dear Ehren,

    no, unfortunately it is not yet working to insert an Image in

    my message.

    Thanks for your help

    Best Regards

    Andreas

    Thursday, February 22, 2018 7:13 AM
  • Hi Andreas. Could you share a screenshot via One Drive or Dropbox and post the link here? (Just don't format it as a link, as the forum may not allow you to do that yet either.)

    Ehren

    Thursday, February 22, 2018 6:46 PM
    Owner
  • Dear Ehren,

    Company policy does not allow usage of one drive and drop box.

    Have you got another idea how we can solve the problem?

    Thanks a lot.

    Best Regards

    Andreas

    Monday, February 26, 2018 7:04 AM
  • Hey Andreas,

    You can use any service that your company allows. This is one that I use often:

    https://imgur.com/upload

    but truthfully, you can use any service that you want/can and then post the url here.

    Monday, February 26, 2018 3:06 PM
  • Hi Andreas. Any luck following the suggestions above, or embedding an image directly?

    Ehren

    Friday, March 9, 2018 12:20 AM
    Owner
  • Dear Ehren,

    one of my best colleague Olaf installed the file sharing.

    With following link you will find the two Pictures as mentioned in my message.  

    https://fileshare.ssl.iscar.com/data/public/cd4c59

    I have following table with different warehouse records.
    See Image Before grouping

    and I would like to Group the warehouses "AA" and "WI" to a new field

    See Image after grouping

    Thanks a lot in advance

    Best Regards

    Andreas

    Monday, March 19, 2018 1:14 PM
  • Hi Andreas,

    What happens to warehouse 93 after pivoting the warehouse column? Grouping is a completely different transformation from what you show.

    Monday, March 19, 2018 5:17 PM
  • Hi Andreas. Try doing the following in PQ:

    • Remove the AvailStock column
    • Select the Whs column and click Transform->Pivot Column
    • In the Pivot Column dialog, set the "Values Column" to StockOnHand
    • Click OK

    Ehren

    Monday, March 19, 2018 9:20 PM
    Owner
  • Dear Ehren,

    thank you so much, we are on the right track.
    The warehouse file contains millions of records and hundred of different warehouses.

    With your help I'm getting the right view but hundreds of column due to List.Sum.
    Is it possible to to filter in the following command single warehouses? For example "AA", "WI", "BA".

    = Table.Pivot(#"Delete column", List.Distinct(#"Delete column"[IWWHS]), "IWWHS", "StockOnHand", List.Sum)

    I know that I can select them manually and delete all others in a new command.

    Thanks a lot for your help.

    Best Regards

    Andreas

    Thursday, March 22, 2018 3:54 PM
  • Hi Andreas. Can you provide more complete before/after example screenshots of what you're looking for? It's not entirely clear to me.

    Ehren

    Thursday, March 22, 2018 5:01 PM
    Owner
  • Dear Ehren,

    thanks for your patient.

    the last Statement in the editor
     #"Pivotierte Spalte" = Table.Pivot(#"Delete column", List.Distinct(#"Delete column"[IWWHS]), "IWWHS", "StockOnHand", List.Sum)

    is providing the good result but there are hundreds of warehouses in the columns not relevant for
    the query. I wonder if I can put in the List.Distinct a filter of only 3 relevant warehouses such as

    IWWHS = "AA", "WI", "BA"

    See Screen shoot in enclosed link.

    Thanks a lot

    Best Regards

    Andreas

    https://fileshare.ssl.iscar.com/data/public/583d19

    Tuesday, March 27, 2018 8:27 AM
  • Excel 2016 Pro Plus with Power Query (aka Get & Transform)
    Filter to 3 warehouses.
    http://www.mediafire.com/file/379f5wofy87e78b/03_27_18.xlsx
    http://www.mediafire.com/file/5hdm7eidmw16zse/03_27_18.pdf

    Tuesday, March 27, 2018 3:14 PM
  • Hi Andreas. In addition to Herbert's solution, you could also do this by filtering the IWWHS column prior to pivoting (via the AutoFilter menu, available by clicking the button to the right of the column name).

    Ehren

    Tuesday, March 27, 2018 5:12 PM
    Owner
  • Dear Ehren,

    thanks a lot.

    Because of Herberts and your Support I could solve my Problem.

    Have a nice day

    Best Regards

    Andreas

    • Marked as answer by AnSchmidt Thursday, April 5, 2018 5:20 AM
    • Unmarked as answer by AnSchmidt Thursday, April 5, 2018 5:20 AM
    Thursday, April 5, 2018 5:20 AM