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
ThomasDay
Impactful Individual
Impactful Individual

Create table of merged columns

Hello all,

I'm looking to accomplish the below picture using a Query.  If it's possible to do, I would very much appreciate a "pattern" that can get me started.  I've tried to do this unsuccessfully by creating a 2 column file and adding the other columns via DAX.  So I'm turning to query to see if it can be done.  If not, I'll create many--2 column files, and relate them--I know I can do that.  Let me know what you think--perhaps many 2 column files is just fine. (I will use them as filters for visuals)  Thank you, Tom

2016-04-20_2120.png

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

Should be able to... Eventually you will have to load something... (that post I think was about someone loading 3 tables that were supposed to be appended - then appending and not wanting the original 3 in the data model just the appended) 

 

Seems to work for me when I add new data to the source

PQ-Step5.png

 

Did you get it to work in your case?

View solution in original post

7 REPLIES 7
Sean
Community Champion
Community Champion

@ThomasDay I think I got it...

 

In the Query Editor => select MeaningID Column => click Pivot Column button => select Value

=> in Advanced Options =>select Don't Aggregate => OK

PQ-Step2.png

Let me know if it works...

ThomasDay
Impactful Individual
Impactful Individual

Hey, @Sean sounds promising--I'll look right now.  And if I create a "connection" to a large data set--but don't loaded into the data model--I can attach other queries (plural) to it, right?  (I know, I should experiment a bit on this, but I'm pretty sure I saw a post of yours about not loading)

Sean
Community Champion
Community Champion

Should be able to... Eventually you will have to load something... (that post I think was about someone loading 3 tables that were supposed to be appended - then appending and not wanting the original 3 in the data model just the appended) 

 

Seems to work for me when I add new data to the source

PQ-Step5.png

 

Did you get it to work in your case?

ThomasDay
Impactful Individual
Impactful Individual

That definitely works.  And without so much as a line of "M".  I can piece this together with an innerjoin to get the table synthesized to the small set of MeaningID's and then it does the job.  Beyond my immediate question, this is VERY useful!! Well done and thank you very much.  Tom

Sean
Community Champion
Community Champion

@ThomasDay Isn't this nice? No line of M? I just hope @ImkeF approves of the solution? 

 

Also let's hope they add intellisense to the Query Editor - will help us to learn M much faster.

Hey guys,

all thumbs up of course 🙂

Pivot is really one of the coolest features - but it really shines in the "unpivot and pivot-back"-combination (you see: I also can no code 🙂 )

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ThomasDay
Impactful Individual
Impactful Individual

It's one of the coolest finds yet...I'm guessing she'd give you a thumbs up as well--for shear inventiveness if nothing else. 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors