Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
melz63017
Employee
Employee

Combine unique ids and turning columns into rows

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:

IDTag Name
1Banner
1English
2People
2Infographic
2Arabic
3English

 

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:

IDTag1Tag2Tag3
1BannerEnglishnull
2PeopleInfographic Arabic
3Englishnullnull

or 

IDBannerEnglishPeopleInfographicArabic
111nullnullnull
2nullnull111
3null1nullnullnull

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!

2 ACCEPTED SOLUTIONS
jahida
Impactful Individual
Impactful Individual

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!

View solution in original post

KGrice
Memorable Member
Memorable Member

@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.

View solution in original post

2 REPLIES 2
jahida
Impactful Individual
Impactful Individual

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
Memorable Member
Memorable Member

@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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.