cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Josh_BI_UK Regular Visitor
Regular Visitor

Reference two or more Columns as Source in new query

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Reference two or more Columns as Source in new query

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Josh_BI_UK Regular Visitor
Regular Visitor

Re: Reference two or more Columns as Source in new query

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.

Stachu Super Contributor
Super Contributor

Re: Reference two or more Columns as Source in new query

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Josh_BI_UK Regular Visitor
Regular Visitor

Re: Reference two or more Columns as Source in new query

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.

 

 

Stachu Super Contributor
Super Contributor

Re: Reference two or more Columns as Source in new query

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
Josh_BI_UK Regular Visitor
Regular Visitor

Re: Reference two or more Columns as Source in new query

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.Smiley Happy

Stachu Super Contributor
Super Contributor

Re: Reference two or more Columns as Source in new query

{} 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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kulchandra Member
Member

Re: Reference two or more Columns as Source in new query

Hi I am not able to change Schema name via parameter. Could you please look in to my original post and help me out? 

 

https://community.powerbi.com/t5/Desktop/Schema-change-to-multiple-queries-SAP-hana-and-Direct-query...

Thanks

kulchandra Member
Member

Re: Reference two or more Columns as Source in new query

can we change the schemna in my all quries at once? via this type of code?
I am not able to change Schema name via parameter. Could you please look in to my original post and help me out? 

https://community.powerbi.com/t5/Desktop/Schema-change-to-multiple-queries-SAP-hana-and-Direct-query...


@Stachu wrote:

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


 


@Stachu wrote:

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


 


@Stachu wrote:

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


 


@Stachu wrote:

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






Thanks

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 218 members 2,503 guests
Please welcome our newest community members: