Collapse specific rows into another row

I

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
751
Office Version
  1. 365
Platform
  1. Windows
given the range below (much bigger in my data) I want to combine/collapse Monday for the week into Tuesdays values for the week per product. currently 2 products. so 4 rows in row 10-13 would collapse into 2 rows, and leave Tuesday and sum Monday into Tuesday for each product. i assume PwrQry is best solution (please advise) thanks

row12; 8/29/2022, TEST_B CDX, 219, 3, Tuesday
row13; 8/29/2022, TEST_A, 131, 3, Tuesday

powerquery_question, MrExcel.xlsx
ABCDE
1dateproducttestweekdayNumWeekDay
28/24/2022TEST_A2264Wednesday
38/24/2022TEST_A2114Wednesday
48/25/2022TEST_B CDX2395Thursday
58/25/2022TEST_A2025Thursday
68/26/2022TEST_B CDX2676Friday
78/26/2022TEST_A1836Friday
88/27/2022TEST_B CDX2337Saturday
98/27/2022TEST_A1857Saturday
108/29/2022TEST_B CDX102Monday
118/29/2022TEST_A42Monday
128/30/2022TEST_B CDX2093Tuesday
138/30/2022TEST_A1273Tuesday
148/31/2022TEST_B CDX2434Wednesday
158/31/2022TEST_A1924Wednesday
169/1/2022TEST_A2015Thursday
179/1/2022TEST_B CDX2395Thursday
189/2/2022TEST_A2356Friday
199/2/2022TEST_B CDX2656Friday
209/3/2022TEST_B CDX2447Saturday
219/3/2022TEST_A1887Saturday
229/6/2022TEST_B CDX83Tuesday
239/6/2022TEST_A53Tuesday
249/7/2022TEST_A1524Wednesday
259/7/2022TEST_B CDX1584Wednesday
269/8/2022TEST_A2165Thursday
279/8/2022TEST_B CDX2265Thursday
289/9/2022TEST_A1636Friday
299/9/2022TEST_B CDX2456Friday
309/10/2022TEST_B CDX2147Saturday
319/10/2022TEST_A1607Saturday
329/12/2022TEST_A182Monday
339/12/2022TEST_B CDX182Monday
349/13/2022TEST_A1443Tuesday
359/13/2022TEST_B CDX1713Tuesday
369/14/2022TEST_A1744Wednesday
379/14/2022TEST_B CDX1974Wednesday
389/15/2022TEST_A1825Thursday
399/15/2022TEST_B CDX2515Thursday
409/16/2022TEST_A1966Friday
419/16/2022TEST_B CDX2466Friday
429/17/2022TEST_A2067Saturday
439/17/2022TEST_B CDX2777Saturday
449/19/2022TEST_A112Monday
459/19/2022TEST_B CDX92Monday
469/20/2022TEST_A1533Tuesday
479/20/2022TEST_B CDX1833Tuesday
489/21/2022TEST_A1514Wednesday
499/21/2022TEST_B CDX2354Wednesday
Sheet4
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"date", type date}}),
    tcn = Table.ColumnNames(tbl),
    f = (x, t, fn) => if t then fn(x) else x,
    tbl1 = Table.FromRecords(Table.TransformRows(tbl, (rcd)=>
            let t = rcd[WeekDay] = "Monday" in 
            Record.TransformFields(rcd, {
                {"date", (x)=> f(x,t, each Date.AddDays(_,1))},
                {"WeekDay", (x)=> f(x,t, each "Tuesday")},
                {"weekdayNum", (x)=> f(x, t, each 3)}   }))),
    tbl2 = Table.Group(tbl1, List.RemoveItems(tcn, {"test"}), {{"test", each List.Sum([test]), type number}}),
    Result = Table.ReorderColumns(tbl2,tcn)
in
    Result

Book1
ABCDEFGHIJKL
1dateproducttestweekdayNumWeekDaydateproducttestweekdayNumWeekDay
28/24/2022TEST_A2264Wednesday8/24/2022TEST_A4374Wednesday
38/24/2022TEST_A2114Wednesday8/25/2022TEST_B CDX2395Thursday
48/25/2022TEST_B CDX2395Thursday8/25/2022TEST_A2025Thursday
58/25/2022TEST_A2025Thursday8/26/2022TEST_B CDX2676Friday
68/26/2022TEST_B CDX2676Friday8/26/2022TEST_A1836Friday
78/26/2022TEST_A1836Friday8/27/2022TEST_B CDX2337Saturday
88/27/2022TEST_B CDX2337Saturday8/27/2022TEST_A1857Saturday
98/27/2022TEST_A1857Saturday8/30/2022TEST_B CDX2193Tuesday
108/29/2022TEST_B CDX102Monday8/30/2022TEST_A1313Tuesday
118/29/2022TEST_A42Monday8/31/2022TEST_B CDX2434Wednesday
128/30/2022TEST_B CDX2093Tuesday8/31/2022TEST_A1924Wednesday
138/30/2022TEST_A1273Tuesday9/1/2022TEST_A2015Thursday
148/31/2022TEST_B CDX2434Wednesday9/1/2022TEST_B CDX2395Thursday
158/31/2022TEST_A1924Wednesday9/2/2022TEST_A2356Friday
169/1/2022TEST_A2015Thursday9/2/2022TEST_B CDX2656Friday
179/1/2022TEST_B CDX2395Thursday9/3/2022TEST_B CDX2447Saturday
189/2/2022TEST_A2356Friday9/3/2022TEST_A1887Saturday
199/2/2022TEST_B CDX2656Friday9/6/2022TEST_B CDX83Tuesday
209/3/2022TEST_B CDX2447Saturday9/6/2022TEST_A53Tuesday
219/3/2022TEST_A1887Saturday9/7/2022TEST_A1524Wednesday
229/6/2022TEST_B CDX83Tuesday9/7/2022TEST_B CDX1584Wednesday
239/6/2022TEST_A53Tuesday9/8/2022TEST_A2165Thursday
249/7/2022TEST_A1524Wednesday9/8/2022TEST_B CDX2265Thursday
259/7/2022TEST_B CDX1584Wednesday9/9/2022TEST_A1636Friday
269/8/2022TEST_A2165Thursday9/9/2022TEST_B CDX2456Friday
279/8/2022TEST_B CDX2265Thursday9/10/2022TEST_B CDX2147Saturday
289/9/2022TEST_A1636Friday9/10/2022TEST_A1607Saturday
299/9/2022TEST_B CDX2456Friday9/13/2022TEST_A1623Tuesday
309/10/2022TEST_B CDX2147Saturday9/13/2022TEST_B CDX1893Tuesday
319/10/2022TEST_A1607Saturday9/14/2022TEST_A1744Wednesday
329/12/2022TEST_A182Monday9/14/2022TEST_B CDX1974Wednesday
339/12/2022TEST_B CDX182Monday9/15/2022TEST_A1825Thursday
349/13/2022TEST_A1443Tuesday9/15/2022TEST_B CDX2515Thursday
359/13/2022TEST_B CDX1713Tuesday9/16/2022TEST_A1966Friday
369/14/2022TEST_A1744Wednesday9/16/2022TEST_B CDX2466Friday
379/14/2022TEST_B CDX1974Wednesday9/17/2022TEST_A2067Saturday
389/15/2022TEST_A1825Thursday9/17/2022TEST_B CDX2777Saturday
399/15/2022TEST_B CDX2515Thursday9/20/2022TEST_A1643Tuesday
409/16/2022TEST_A1966Friday9/20/2022TEST_B CDX1923Tuesday
419/16/2022TEST_B CDX2466Friday9/21/2022TEST_A1514Wednesday
429/17/2022TEST_A2067Saturday9/21/2022TEST_B CDX2354Wednesday
439/17/2022TEST_B CDX2777Saturday
449/19/2022TEST_A112Monday
459/19/2022TEST_B CDX92Monday
469/20/2022TEST_A1533Tuesday
479/20/2022TEST_B CDX1833Tuesday
489/21/2022TEST_A1514Wednesday
499/21/2022TEST_B CDX2354Wednesday
50
Sheet3
 
Upvote 0
thanks. works as expected. M language seems very powerful but also difficult to understand, so thanks

side question... once i have my transformations, is there a way M language (PQ) can export to csv? i see solutions that involve DAX Studio and manually selecting "Export File", looking for an automated solution thru PQ. Possible? thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Slotting Calculation Challenge
Replies
3
Views
137
dcbuzzell
D
L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
BrettOlbrys1
  • Question Question
PowerBI - Complicated issue or Not?
Replies
3
Views
231
JGordon11
JGordon11
S
  • Question Question
Historical Dep Dates Calculation in Power BI
Replies
2
Views
153
shansakhi
S
J
  • Solved
Power Query IF Function combined with NETWORKDAYS.INTL
Replies
1
Views
162
alansidman
A

Forum statistics

Threads
1,223,603
Messages
6,173,302
Members
452,509
Latest member
CSHOCK

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top