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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.