Combine unique ids and turning columns into rows

[melz63017]

I have a list of post IDs from WordPress and tags associated with them. Currenlty if there are multiple tags per post then that post has multiple entries:

ID	Tag Name
1	Banner
1	English
2	People
2	Infographic
2	Arabic
3	English

I would like to filter out all the posts that don't have an English Tag so I would only keep post 1 and 3.

The way I thought about doing this is combining the ID rows like this:

ID	Tag1	Tag2	Tag3
1	Banner	English	null
2	People	Infographic	Arabic
3	English	null	null

or 

ID	Banner	English	People	Infographic	Arabic
1	1	1	null	null	null
2	null	null	1	1	1
3	null	1	null	null	null

and filtering out posts that are not in English.

What are the Queries/transformations that would get me to those last two tables?

Alternatively, is there a better way to filter out all non-English posts?

Thanks!

[jahida · Accepted solution]

I think your current layout is better than either of the two alternatives you posted, so in terms of how to Filter the records in that format, try something like this (this assumes Changed Type is the name of the previous step, change that as necessary):

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Tag Name] = "English")),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Table.Contains(#"Filtered Rows", [ID = [ID]]))

The first step you can do using the query wizard (just use a filter on Tag Name). The second one you'll have to do partially manually. The easiest way to do it is probably just make a step (filter something random) and then replace the code.

Hope that helps!

[jahida · Accepted solution]

I think your current layout is better than either of the two alternatives you posted, so in terms of how to Filter the records in that format, try something like this (this assumes Changed Type is the name of the previous step, change that as necessary):

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Tag Name] = "English")),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Table.Contains(#"Filtered Rows", [ID = [ID]]))

The first step you can do using the query wizard (just use a filter on Tag Name). The second one you'll have to do partially manually. The easiest way to do it is probably just make a step (filter something random) and then replace the code.

Hope that helps!

[KGrice · Accepted solution]

@jahida's answer will work, and is probably more efficient than what I'm about to post. I'm only including it here because I hadn't tried a problem like yours before, and this is what I came up with. I think it's neat that there are so many ways to get to an answer with M.

I added a custom column with the formula below, also assuming that the previous step was Changed Type:

isEnglish = Table.AddColumn(#"Changed Type", "Custom", each List.Contains(Table.FindText(#"Changed Type", "English")[ID], [ID]))

That leaves you with a TRUE/FALSE column that you can filter for just TRUE to leave you with posts in English.