Create table of merged columns

[ThomasDay]

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

[Sean]

@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

Let me know if it works...

[ThomasDay]

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 · Accepted solution]

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

Did you get it to work in your case?

[ThomasDay]

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]

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

[ImkeF]

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 🙂 )

[ThomasDay]

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.