SOLVED

Expanding Meals Count on each date

%3CLINGO-SUB%20id%3D%22lingo-sub-2584014%22%20slang%3D%22en-US%22%3EExpanding%20Meals%20Count%20on%20each%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584014%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20set%20of%20data%20here%20down%20below%20in%20table%201.%20(Attached%26nbsp%3B%20the%20excel%20too)%3C%2FP%3E%3CP%3EI%20want%20to%20prepare%20Table%202%20based%20on%20the%20date%20in%20table%201.%20ie.%2C%20I%20want%20to%20expand%20the%20data%20from%20Table%201%20based%20on%20from%20date%20to%20end%20date%20and%20meals%20count%20in%20these%20days%20(Breakfast%2C%20Lunch%20and%20Dinner).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20particular%20formula%2F%20power%20Queries%20to%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sameer_Kuppanath_Sultan_0-1627194759385.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298211iED057C74C2904A02%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sameer_Kuppanath_Sultan_0-1627194759385.png%22%20alt%3D%22Sameer_Kuppanath_Sultan_0-1627194759385.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2584014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2584377%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Meals%20Count%20on%20each%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version%20for%20a%20solution%20using%20formulas.%3C%2FP%3E%0A%3CP%3EI%20also%20modified%20the%20formulas%20in%20Table%201%20slightly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2584726%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Meals%20Count%20on%20each%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20everyone's%20permission%2C%20if%20I%20may%20add%20a%20simple%20formula%20in%20addition%20to%20the%20elegant%20solution%20proposed%20by%20Mr.%20Hans%20Vogelaar%20(%40Hans%20Vogelaar).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DSUMIF(%24D%243%3A%24D%2421%2C%24M3%2C%24H%243%3A%24H%2421)%2BSUMIF(%24F%243%3A%24F%2421%2C%24M3%2C%24H%243%3A%24H%2421)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EFile%20is%20inserted.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20the%20simple%20suggested%20solution%20helps%20you%2C%20I'll%20be%20happy%2C%20if%20not%20please%20just%20ignore%20it%20%3A).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hi All

 

I have a set of data here down below in table 1. (Attached  the excel too)

I want to prepare Table 2 based on the date in table 1. ie., I want to expand the data from Table 1 based on from date to end date and meals count in these days (Breakfast, Lunch and Dinner).

 

Is there any particular formula/ power Queries to do this? 

Sameer_Kuppanath_Sultan_0-1627194759385.png

 

 

3 Replies
best response confirmed by Sameer_Kuppanath_Sultan (Frequent Contributor)
Solution

@Sameer_Kuppanath_Sultan 

See the attached version for a solution using formulas.

I also modified the formulas in Table 1 slightly.

@Sameer_Kuppanath_Sultan 

With everyone's permission, if I may add a simple formula in addition to the elegant solution proposed by Mr. Hans Vogelaar (@Hans Vogelaar).

=SUMIF($D$3:$D$21,$M3,$H$3:$H$21)+SUMIF($F$3:$F$21,$M3,$H$3:$H$21)

 

File is inserted.

If the simple suggested solution helps you, I'll be happy, if not please just ignore it :).

 

Thank you for your understanding and patience

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)

Excellent!