Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi PWRBI family,
How do you references two columns from a table in another query as the source for a new query using Power BI - Query editor?
Example
Query A > Table called: "Tbl_CoursesbyTutorTable", Columns needed called: "Course Codes" and "Course Title"
New Query (B) > References only columns "Course Codes" and "Course Title" from Query A.
I don't want to use the Reference shortcut function or Duplicate the original query.
I would like to simply create a new query using M (Advanced Editor), and have some code similar to this, but for two columns, instead of just one:
let Source = Tbl_CoursesbyTutorTable[Course Title] in Source
Mega appreciate the help in advance.
Solved! Go to Solution.
I speak from personal experience - if there is difference in performance I don't think it will be visible in most cases
I do prefer the clarity of the code in my syntax, it's quite straightforward so in case you need to handover it shouldn't be too complex
as for learning you can give this a go
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification
a bit tough read, but gives much better understanding of M
EDIT
you can even simplify it more
let Source = Table.SelectColumns(Tbl_CoursesbyTutorTable,{"Course Codes", "Course Title"}) in Source
but I actually prefer to have the steps separated
Could you bring into two tables as a source?
= Table.SelectColumns(Table 1, {"OwnerId", "Type", "CreatedDate", "Creator_Role__c"}) and somehow have another Table.SelectColumns for Table 2?
Hi @actzikas, I hope I'm interpreting your question, right? From the looks of it you wish to Join or Combine two tables into one query?
I don't think this post best suits your question. May I suggest looking at this post Append vs Merge using Power Query ; or if that doesn't help please add further clarification.
Teamwork makes the BI work - always happy to help🙌
Hi all, had a quick mess about and got something to work. I referenced one column using:
let
#"QueryA_Col_1" = Tbl_CoursesbyTutorTable[Course Title]
in
#"QueryA_Col_1"
then I edited the query and added a custom column which used my 2nd column as a source.
Code I used:
let #"QueryA_Col_1" = Tbl_CoursesbyTutorTable[Course Title], #"Converted to Table" = Table.FromList(#"QueryA_Col_1", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"QueryA_Col_2" = Table.AddColumn(#"Converted to Table", "Course", each Tbl_CoursesbyTutorTable[Course Codes]), #"Expanded Course Codes Col" = Table.ExpandListColumn(#"QueryA_Col_2", "Course Codes") in #"Expanded Course Codes Col"
Is there a cleaner / slimier code I could use? My code worked but looks clumsy.
I'd reference the whole table, and then select only valid columns in next step
let Source = Tbl_CoursesbyTutorTable, #"Removed Other Columns" = Table.SelectColumns(Source,{"Course Codes", "Course Title"}) in #"Removed Other Columns"
Hi @Stachu,
Thank you for sharing. Happy to go with your suggestion, if there is a reason why it is a better approach e.g. speed of processing, best practice, your experience etc.
Could you elaborate, so this post acts as a learning tool also? Any blogs mentioning the topics would also be fab.
I speak from personal experience - if there is difference in performance I don't think it will be visible in most cases
I do prefer the clarity of the code in my syntax, it's quite straightforward so in case you need to handover it shouldn't be too complex
as for learning you can give this a go
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification
a bit tough read, but gives much better understanding of M
EDIT
you can even simplify it more
let Source = Table.SelectColumns(Tbl_CoursesbyTutorTable,{"Course Codes", "Course Title"}) in Source
but I actually prefer to have the steps separated
Thank you @Stachu , this works like a charm. I tried to apply the "{ }" curly brackets methodology, before your post but got the syntax wrong. Thank you, your code clears it up for me.
{} is the syntax for list e.g. {1..10} creates list from 1 to 10, while {"a","x","c"} is just hardcoded one
in the syntax i posted, if one of the preceding steps would contain the column names you could reference that list in Table.SelectColumns
I use it sometimes for dynamic column selection
User | Count |
---|---|
85 | |
84 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |