Problem with filtering the null values and independence of the conditional columns

[Ella123]

Spoiler

Hello, could someone help me out ? I have this "value" column created from unpivoting , which includes the roles (engineer etc.) and also the names of the people.My goal is to have the roles in one column and the names another column.To do this i have created the index column to use to seperate the roles and the names as the roles ends in index 73 ,then begins the names.Using this through conditional column creation i was able to seperate the two in the two columns "Role" and "Name".In the role column for example the index > 73 are replaced with null values , in the name column the opposite.But now to have the corresponding names and roles matched ( i mean they should be next to each other in a row) , i need to remove the null values .But the problem arises here.When i filter out the null values in one column (roles) in this case , the name column is also filtered , which defies the purpose.I also tried removing the attribute column and then filtering but nothing changes .

The data is dummy data.

Below is after filtering the nulls in the role column .

I'll appreciate any suggestions,thanks !

[Anonymous · Accepted solution]

Hi @Ella123 ,

But now to have the corresponding names and roles matched ( i mean they should be next to each other in a row)

What is the matching rule for names and roles? Is it a one-to-one match from top to bottom? (For example, the role with Index = 1 corresponds to the name with Index = 74)

If yes, you can try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdQ7aiRBEIThu7QtoyOznqYK1tRa6w26/zVWsxnREAvDwF+g5qtWMK/Xhevj+vr8+bq+P15XWKVVs+pWw2paLatthdvTNXAO3AMHwUVwEtwER8FV4ar47x25KlwVb9Wv3092z+E5PZfntszbE57hmZ6uSlelq9JV6ap0VXNV+6f682R4pmfz7J7Dc3ouz23Zb09XdVd1V3VXdVd1V3VXdVd1Vw1XDVcNVw1XDVcNVw1XDVcNVw1XTVdNV01XTVfNt+rn84nKzozKwczKyWyVi9krN3PUr8bNnJVgrspg7soU465+WOVacqFgSzCUbEmGoi3RULYlGwq3hUPptnQo3hYP5dvyRfm2fFG+/by38m35onxbvijfli/Kt+WLwZ9VAWPyQMJYPBAxNg9kzJsHQiZ4IGUGD57/b/JAzmw8EDQ7DyRNSiFpUgpJk1JImpRC0kYpJG2UQtJGKSRtlOLZIqWg9OgRhB4+Ieg8fECQefj3QeXhRYPIw3sGjYfXDBIPbxkUHl4ypjy8ZDxACkNCjhopIleNlJGzRgrJXSOl5LCRYnLZSDk5baSg3DZSUo4bKSnXjXzeJaVNUu4bTVIOHE1SLhxNUk28SaqJN0k18Sbpe+LffwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Roles = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Roles", type text}, {"Name", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Roles", "Name"}),
    AddModifiedNamesColumn = Table.AddColumn(#"Replaced Value", "Modified Names", each if [Index] <= 73 then try #"Replaced Value"[Name]{[Index] + 73 - 1} otherwise null else null),
    #"Removed Columns" = Table.RemoveColumns(AddModifiedNamesColumn,{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Roles] <> null))
in
    #"Filtered Rows"

If this is not what I understand, please explain in detail how to match name and role, thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I would make the last two columns a new table, by duplicating the table, and selecting the last two columns. Then, use Table.SplitAt to split the table at row 74 or whichever index you choose. Then remove the nulls from the last column. Then, just make a new index on the last column and merge it back in based on index. 

--Nate

[sunate]

Could you share the original data before unpivoting?