Remove blanks from columns but maintain relationships

[Anonymous]

Hello.  I have what looks like a simple problem, but has multple dimensions to it. 

Problem:  I’m importing a .csv file that contains two columns; 1. People and 2. Projects but there are blank records in both columns.  For example, the People column has a single entry for a Person but then there are one or more blank records until there's a new project that the previous person has no association with.  At that point, there's a new Person identified and it's blank again until the Project column has a value that doesn't apply to that person.  Unfortunately, I can’t just remove the blanks without impacting the other columns (EXCEPT in the case where the People record is populated and the Project record is blank – that row can be removed without impact as long as I don't lose the Person's name for the subsequent Projects.  Any help or suggestions would be greatly appreciated.

BTW, I created a picture of the table, which might help to visualize the problem but I wasn't able to apply it to this post.  I can email it to anyone who would like.

Thank you in advance for any help.

[Anonymous]

Here's the image I wanted to show.  Hopefully this clears up some of the details.

[zoloturu · Accepted solution]

Hi @Anonymous,

To do what you need:

1. Do right click on header People 
2. Select menu there Replace Values, where leave Value to Find empty, Replace With enter word null
3. Repeat step 1.
4. Select menu there Fill -> Down
5. Open a filter near to header Project 
6. Unselect (blank) valuesremove blanks

   And here is M script for your reference. I've manually entered your values to be able to show result better. I've formatted needed changes in bold.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PT89MVdJRUorViVYC0o4whhOM4QxjuIAZIUWlydmpRUhaXMEM38SiSmzmQGQDEkuQJN2gOtJTEpFE4Ra5K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [People = _t, Project = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"People", type text}, {"Project", type text}}),


    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"People"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"People"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Project] <> ""))
in
    #"Filtered Rows"

[Anonymous]

Hi Zoloturu.  Thanks for the help.  This works perfect and is much easier than I thought it would be!  I appreciate the images.  Those were a big help.  - Bingo

[zoloturu]

@Anonymous, you are welcome.