SOLVED

Loop Command in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1741468%22%20slang%3D%22en-US%22%3ELoop%20Command%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741468%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20to%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStuck%20up%20with%20large%20set%20of%20data%20which%20has%20multiple%20variables%20and%20overlapping%20sets%20of%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20overcome%20the%20same%2C%20Through%20%22Power%20Query%22%2C%20I%20have%20managed%20to%20get%20non-overlapped%20value%20for%20single%20item%20in%20sheet%20'Length%20Achieved%20-%20Single%20Item'.%20(Item1%20-%20A%20is%20having%20value%20of%20400)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I'm%20unable%20to%20apply%20the%20same%20with%20Multiple%20Item%20in%20sheet%20'Length%20Achieved%20-%20Multiple%20Item'..%20((Item1%20-%20A%20value%20changes%20to%20950)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20in%20Running%20Same%20Sets%20of%20Command%20in%20Loop.%20Sheet%20Attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20Advance..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1741468%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741829%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20Command%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159916%22%20target%3D%22_blank%22%3E%40aditya%3C%2FA%3E%26nbsp%3BNot%20sure%20if%20I%20fully%20comprehended%20your%20problem%2C%20but%20perhaps%20the%20attached%20workbook%20contains%20the%20result%20you%20are%20after.%20I've%20added%20a%20query%20%22Table1%20(2)%22%20that%20load%20a%20four%20row%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741980%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20Command%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Attached%20solves%20one%20part%20of%20problem%20of%20looping.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20It%20is%20not%20removing%20and%20capturing%20overlapping%20%26amp%3B%20non-overlapping%20data.%20Sample%20data%20for%20single%20item%20is%20shown%20below.%20And%20Power%20Query%20of%20Single%20Item%20is%20attached%20in%20previous%20post%20named%20as%20%22Length%20Achieved%20-Single%20Item%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22656px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3E%3CSTRONG%3EData%20with%20same%20set%20of%20(ITEM%20%26amp%3B%20SIDE)%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E%3CSTRONG%3EResultant%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3ESide%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3EStart%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3EEnd%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3EValue%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3ESide%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3EStart%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3EEnd%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3ELENGTH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E100%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E300%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EItem1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EA%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E100%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E400%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E300%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E250%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EItem1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EA%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E500%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E600%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E100%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E100%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E150%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EItem1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EA%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E750%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E850%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E100%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E500%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E600%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EItem1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3EA%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E900%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E1000%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%3CFONT%20color%3D%22%2300FF00%22%3E100%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E280%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E400%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E750%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E800%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%2300FF00%22%3E%3CSTRONG%3EItem1%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%2300FF00%22%3E%3CSTRONG%3EA%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%2300FF00%22%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%2300FF00%22%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%3CFONT%20size%3D%225%22%20color%3D%22%2300FF00%22%3E%3CSTRONG%3E600%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E800%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E850%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2260.0833px%22%3EItem1%3C%2FTD%3E%3CTD%20width%3D%2299.9px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2281.7333px%22%3E900%3C%2FTD%3E%3CTD%20width%3D%2245.5333px%22%3E1000%3C%2FTD%3E%3CTD%20width%3D%2250.5167px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2276.7px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2241.3667px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2244.0333px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2245.65px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269.4833px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1742037%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20Command%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159916%22%20target%3D%22_blank%22%3E%40aditya%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20the%20attached%20file.%20Split%20off%20the%20original%20query%20and%20loaded%20two%20tables.%20One%20before%20aggregation%20and%20one%20after.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1742460%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20Command%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159916%22%20target%3D%22_blank%22%3E%40aditya%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20to%20receive%20such%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20323px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223933iAB0712A24636C1BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20ignoring%20column%20Value%20in%20source%20data%2C%20not%20sure%20for%20what%20it%20is%20needed%2C%20we%20may%20generate%20list%20from%20start%20to%20end%20for%20each%20record%2C%20group%20table%20on%20Item%20and%20Side%2C%20as%20aggregation%20union%20lists%20for%20the%20groups%20and%20count%20them.%20Entire%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Add%20column%20with%20lists%20of%20points%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22List%20values%22%2C%0A%20%20%20%20%20%20%20%20each%20%7B%5BStart%5D%2B1..%5BEnd%5D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Groupe%20by%20Items%20and%20Sides%20and%20count%20union%22%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20%23%22Add%20column%20with%20lists%20of%20points%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Item%22%2C%20%22Side%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Count%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20each%20List.Count(List.Union(%5BList%20values%5D))%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Groupe%20by%20Items%20and%20Sides%20and%20count%20union%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi to all,

 

Stuck up with large set of data which has multiple variables and overlapping sets of values.

 

To overcome the same, Through "Power Query", I have managed to get non-overlapped value for single item in sheet 'Length Achieved - Single Item'. (Item1 - A is having value of 400)

 

However, I'm unable to apply the same with Multiple Item in sheet 'Length Achieved - Multiple Item'.. ((Item1 - A value changes to 950)

 

I need help in Running Same Sets of Command in Loop. Sheet Attached.

 

Thanks in Advance..

7 Replies
Highlighted

@aditya Not sure if I fully comprehended your problem, but perhaps the attached workbook contains the result you are after. I've added a query "Table1 (2)" that loads a four row table.

Highlighted

@Riny_van_Eekelen  Attached solves one part of problem of looping.

 

However, It is not removing and capturing overlapping & non-overlapping data. Sample data for single item is shown below. And Power Query of Single Item is attached in previous post named as "Length Achieved -Single Item".

 

Data with same set of (ITEM & SIDE) Resultant
ItemSideStartEndValue ItemSideStartEndLENGTH
Item1A1003001 Item1A100400300
Item1A2002501 Item1A500600100
Item1A1001501 Item1A750850100
Item1A5006001 Item1A9001000100
Item1A2804001      
Item1A7508001 Item1A  600
Item1A8008501      
Item1A90010001      

 

 

Highlighted

@aditya Perhaps like in the attached file. Split off the original query and loaded two tables. One before aggregation and one after.

Highlighted
Best Response confirmed by aditya (Occasional Contributor)
Solution

@aditya 

As variant, to receive such result

image.png

and ignoring column Value in source data, not sure for what it is needed, we may generate list from start to end for each record, group table on Item and Side, as aggregation union lists for the groups and count them. Entire script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Add column with lists of points" = Table.AddColumn(
        Source,
        "List values",
        each {[Start]+1..[End]}
    ),
    #"Groupe by Items and Sides and count union" = Table.Group(
        #"Add column with lists of points",
        {"Item", "Side"},
        {
            {"Count",
             each List.Count(List.Union([List values]))
            }
        }
    )
in
    #"Groupe by Items and Sides and count union"
Highlighted
Thanks, Works exactly with what I was looking for..

I'm beginner level for Power Query, Can you suggest some good book / weblinks for getting more hands on Power Query Advance Commands.
Highlighted

@Riny_van_EekelenThanks for the efforts mate.. But it misses ranges between multiple data set as under.

Item1A100300
Item1A200250
Item1A100150
Item1A500600
Item1A280400
Item1A750800
Item1A800850
Item1A9001000

 

Should provide resultant as

 

MergedStartEndSubtraction
Item1|A100400300
Item1|A500600100
Item1|A750850100
Item1|A9001000100

 

Hence, Total of Item1|A shall be 600.

 

Solution provided by @Sergei Baklan covers the same.

 

Thanks Amigos.

Highlighted

@aditya 

You are welcome, glad to help. As for resources - lot of them. You may start from Gil Raviv book https://datachant.com/next/ and his blog, lot of topics on Ken Puls' excelguru.ca , next could be Chris Webb' BI blog.