cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msoehnchen Frequent Visitor
Frequent Visitor

How to add values xx, yy from Column A as new values in Column C and Column D

HI everyone,

 

i'm kinda stuck. Just to let u know, i'm fairly new to Power Query. I want to add new columns (in pic.2 its "CT1" and "CT3") which contains the values of Colum "Counted Qty" from all following records with the same value in Column "Location". I cant get it to work. Tried searching and googling but couldnt find a solution. I guess that can be achieved in M-Code, but i really cant work it out.

 

Would be very nice if someone could give me a hint (or even a solution)... 

 

formumSheetOriginal.JPGOriginalformumSheetResult.JPGresult ?

Sample Data:

 

DateTransaction TimeList IDLocationSKUTag IDOriginal QtyUpdate QtyCounted QtyTempTag
10/07/20190.49130800000446081C08175BB29059475G01326670459-1049G013266704
10/07/20190.48893500000446001C08175BB29059475G013266704491059G013266704
10/07/20190.40350700000445981C08175BB29059475G01326670448149G013266704
10/07/20190.37530100000445981C08161BBABW5X50G013553562606G013553562
10/07/20190.37494200000445981C08162BB76689150G01237894323023G012378943
10/07/20190.37425900000445981C08163BBNOINVENTORY  0 1C08163BB-NOINVENTORY
10/07/20190.37414400000445981C08164BB76689146G01237893724024G012378937
10/07/20190.37329900000445981C08165BB76689078G01237894013013G012378940
10/07/20190.37231500000445981C08166BB76689145G01237892324024G012378923
10/07/20190.37076400000445981C08171BBNOINVENTORY  0 1C08171BB-NOINVENTORY
10/07/20190.37067100000445981C08173BBNOINVENTORY  0 1C08173BB-NOINVENTORY
10/07/20190.36858800000445981C08176BBNOINVENTORY  0 1C08176BB-NOINVENTORY
10/07/20190.36847200000445981C08181BBNOINVENTORY  0 1C08181BB-NOINVENTORY
10/07/20190.3683800000445981C08182BB76687981G013050341404G013050341
10/07/20190.36700200000445981C08184BB155430G01326514067067G013265140
10/07/20190.36621500000445981C08185BBNOINVENTORY  0 1C08185BB-NOINVENTORY
10/07/20190.36615700000445981C08186BBNOINVENTORY  0 1C08186BB-NOINVENTORY
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: How to add values xx, yy from Column A as new values in Column C and Column D

Hi @msoehnchen 

1. Copy the table 1 to get Table 2

2. In Table 2,

    group table by "location"->Split Column by Delimiter->Remove first Columns after the "location"

3.png

 

3. In Table1

   Add Index->

  Grouped Rows by location, meanwhile add a minium index column based on each group->

  Expand AllRows->

  Filtered Rows where minium index column=index column->

  Merged Queries for Table1 and Table 2 based on "location"->

  Expand Table

4.png

 

Reference:

https://community.powerbi.com/t5/Desktop/Split-a-cell-values-in-a-column-to-multiple-columns-by-valu...

https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

https://social.technet.microsoft.com/Forums/en-US/e5a98a17-115e-4759-8636-46e6db76b87b/index-startin...

https://social.technet.microsoft.com/Forums/en-US/9484372b-b667-4890-a5dd-52657aa574cd/finding-a-min...

 

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

msoehnchen Frequent Visitor
Frequent Visitor

Re: How to add values xx, yy from Column A as new values in Column C and Column D

Thanks Maggie,
i tried it yesterday by myself and i also came to the conclusion, that only a merge of that table with itself could help. My fault was, that i tried to achieve this whole thing in one query. But this way, i learned a lot 🙂

 

I did it slightly different than your solution, but i will try your way the next time. I also appreciate the links - very helpful.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to add values xx, yy from Column A as new values in Column C and Column D

Hi @msoehnchen 

1. Copy the table 1 to get Table 2

2. In Table 2,

    group table by "location"->Split Column by Delimiter->Remove first Columns after the "location"

3.png

 

3. In Table1

   Add Index->

  Grouped Rows by location, meanwhile add a minium index column based on each group->

  Expand AllRows->

  Filtered Rows where minium index column=index column->

  Merged Queries for Table1 and Table 2 based on "location"->

  Expand Table

4.png

 

Reference:

https://community.powerbi.com/t5/Desktop/Split-a-cell-values-in-a-column-to-multiple-columns-by-valu...

https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

https://social.technet.microsoft.com/Forums/en-US/e5a98a17-115e-4759-8636-46e6db76b87b/index-startin...

https://social.technet.microsoft.com/Forums/en-US/9484372b-b667-4890-a5dd-52657aa574cd/finding-a-min...

 

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

msoehnchen Frequent Visitor
Frequent Visitor

Re: How to add values xx, yy from Column A as new values in Column C and Column D

Thanks Maggie,
i tried it yesterday by myself and i also came to the conclusion, that only a merge of that table with itself could help. My fault was, that i tried to achieve this whole thing in one query. But this way, i learned a lot 🙂

 

I did it slightly different than your solution, but i will try your way the next time. I also appreciate the links - very helpful.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors