union in advance editor

Posts: 1
Joined: Wed Jun 19, 2019 10:59 am

union in advance editor

Postby tiger19 » Wed Jun 19, 2019 11:09 am

hi, i would like to union in power query advanced editor. currently i call my input as parameter as below:

Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
Source = Sql.Database("[servername]", "[databasename]", [Query="SELECT * FROM tablename WHERE year= " & Number.ToText(Parameter[Year]{0}) ] )

How can i add in the where clause in my union?
year=2019 to year= " & Number.ToText(Parameter[Year]{0})

Keep getting error.

Source = Sql.Database("[servername]", "[databasename]",
[Query=" select total 'Total',convert(nvarchar,[value])[value] #(lf) ,#(lf) from tablename #(lf) where total='categorya' and year='2019' #(lf)#(lf) union all#(lf)#(lf) select '' 'total',''year ,''value "])

Posts: 1092
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: union in advance editor

Postby MattAllington » Wed Jun 19, 2019 1:56 pm

It seems you are trying to use Power Query as a SQL authoring tool. Why not
1. just write a View in SQL and connect to that, or
2. use Power Query to do what you want without writing SQL.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 0 guests