split multiple columns to multiple rows while matching base data

%3CLINGO-SUB%20id%3D%22lingo-sub-2629372%22%20slang%3D%22en-US%22%3Esplit%20multiple%20columns%20to%20multiple%20rows%20while%20matching%20base%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629372%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20suppose%2C%20in%20an%20organization%2C%20multiple%20team%20members%20are%20working%20on%20multiple%20projects.%20For%20some%20reason%2C%20whoever%20entered%20the%20data%20did%20it%20in%20the%20following%20way%20shown%20in%20the%20below%20screenshot.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-09%20165346.png%22%20style%3D%22width%3A%20795px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301898iC9676B5C44ADC295%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-09%20165346.png%22%20alt%3D%22Screenshot%202021-08-09%20165346.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3BWe%20can%20imagine%20there%20are%20thousand%20of%20people%20on%20the%20list.%20For%20the%20simplicity's%20sake%2C%20I've%20put%20just%20two%20people.%20So%20Kelly's%20been%20working%20on%20three%20projects%20D%20E%20F%20with%20all%20different%20starting%20dates%2C%20exit%20dates%2C%20and%20reasons%20for%20exit.%20Mike%20has%20been%20working%20on%20projects%20A%2C%20B%2C%20C%2C%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thing%20is%2C%20Mike%20hasn't%20exited%20from%20project%20B%2C%20so%20the%20table%20above%20only%20shows%20start%2Fexit%20info%20for%20projects%20A%20and%20C%20only.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20split%20those%20columns%20into%20multiple%20rows%2C%20while%20matching%20all%20the%20relevant%20info%20(especially%20for%20Mike's%20project%20A%20and%20C%20data).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%2C%20using%20Power%20Query%2C%20I%20was%20able%20to%20do%20the%20following%20(SplitText%20in%20the%20list%20for%20each%20column%20I%20want%20to%20split%2C%20then%20create%20TableFromColumns)%2C%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-09%20165220.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301901iF6137269088D8293%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-09%20165220.png%22%20alt%3D%22Screenshot%202021-08-09%20165220.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%20and%20then%20expanding%20the%20nested%20table%2C%20finally%20I%20got%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-09%20165307.png%22%20style%3D%22width%3A%20861px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301903iD717F00B81CEB5AE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-09%20165307.png%22%20alt%3D%22Screenshot%202021-08-09%20165307.png%22%20%2F%3E%3C%2FSPAN%3EFor%20Kelly%2C%20this%20is%20what%20I'm%20looking%20for.%20But%20for%20Mike%2C%20as%20you%20can%20see%2C%20there's%20a%20mismatch%20because%20of%20the%20missing%20data%20for%20project%20B.%20As%20you%20can%20expect%2C%20at%20the%20end%20of%20the%20day%20what%20I%20want%20is%20something%20like%20the%20following%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-09%20WhatIWant.png%22%20style%3D%22width%3A%20470px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301905i65257CBCB1010216%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-09%20WhatIWant.png%22%20alt%3D%22Screenshot%202021-08-09%20WhatIWant.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20Mike's%20row%20on%20B%20project%20shows%20an%20empty%20cell%20for%20the%20Exit%20Date%20column%20and%20Reason%20column%20because%20he's%20still%20on%20that%20project.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20I%20can%20transform%20the%20original%20sheet%20into%20what%20I%20want%3F%20Thank%20you!%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%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-2629372%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647740%22%20slang%3D%22en-US%22%3ERe%3A%20split%20multiple%20columns%20to%20multiple%20rows%20while%20matching%20base%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647740%22%20slang%3D%22en-US%22%3Eput%20a%20bogus%20end%20date%20for%20project%20B%20like%201%2F1%2F99%20then%20the%20data%20should%20be%20in%20their%20proper%20place%20after%20you're%20done%20manipulating%20it%20in%20power%20query.%20Then%20you%20can%20filter%20out%20the%20projects%20with%20bogus%20dates%20and%20have%20them%20in%20a%20different%20tables.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648135%22%20slang%3D%22en-US%22%3ERe%3A%20split%20multiple%20columns%20to%20multiple%20rows%20while%20matching%20base%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124361%22%20target%3D%22_blank%22%3E%40deadlock132132%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20identify%20projects%20for%20start%20and%20end.%20As%20variant%3C%2FP%3E%0A%3CP%3E-%20keep%20table%20with%20created%20lists%20as%20it%20is%20now%3C%2FP%3E%0A%3CP%3E-%20reference%20it%20and%20extract%20projects%20for%20start%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%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303255i4E34F5B707763522%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20reference%20again%2C%20zip%20end%20with%20reason%20and%20extract%20projects%20for%20them%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%20836px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303256i15ACE41B2E650B13%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20merge%20above%20on%20ID%20and%20Project%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%20274px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303257i9B76656AE9AC1085%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20details%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

So suppose, in an organization, multiple team members are working on multiple projects. For some reason, whoever entered the data did it in the following way shown in the below screenshot. 

 

Screenshot 2021-08-09 165346.png We can imagine there are thousand of people on the list. For the simplicity's sake, I've put just two people. So Kelly's been working on three projects D E F with all different starting dates, exit dates, and reasons for exit. Mike has been working on projects A, B, C, and so on. 

 

The thing is, Mike hasn't exited from project B, so the table above only shows start/exit info for projects A and C only.

 

I would like to split those columns into multiple rows, while matching all the relevant info (especially for Mike's project A and C data).

 

So far, using Power Query, I was able to do the following (SplitText in the list for each column I want to split, then create TableFromColumns),  

Screenshot 2021-08-09 165220.png  and then expanding the nested table, finally I got 

 

Screenshot 2021-08-09 165307.pngFor Kelly, this is what I'm looking for. But for Mike, as you can see, there's a mismatch because of the missing data for project B. As you can expect, at the end of the day what I want is something like the following: 

Screenshot 2021-08-09 WhatIWant.png

 

where Mike's row on B project shows an empty cell for the Exit Date column and Reason column because he's still on that project. 

 

 

 

 

Is there anyway I can transform the original sheet into what I want? Thank you!   

 

 

 

 

1 Reply

@deadlock132132 

You need to identify projects for start and end. As variant

- keep table with created lists as it is now

- reference it and extract projects for start

image.png

- reference again, zip end with reason and extract projects for them

image.png

- merge above on ID and Project

image.png

Please check details in attached.