let
vSrcQuery = “Select * From [dbo].[vw_SalesSummary]”,
Source = Sql.Databases(ServerName),
db_Name = Source{[Name=DatabaseName]}[Data],
// Applying Query Folding to view Native Query
QryFolding = Value.NativeQuery(db_Name, vSrcQuery , null, [EnableFolding=true]),
RemoveColumns = Table.RemoveColumns(QryFolding,{“CntryNam”, “Gross_Sales”, “NetSales”}),
FilterRows = Table.SelectRows(RemoveColumns, each ([FY_Year] = “FY2015”)),
MergeQueries = Table.NestedJoin(FilterRows, {“Row_Id”}, ds_SalesReference, {“Row_Id”}, “ds_SalesReference”, JoinKind.Inner),
ExpandColumns = Table.ExpandTableColumn(MergeQueries, “ds_SalesReference”, {“CntryNam”, “Gross_Sales”, “NetSales”}, {“CntryNam”, “Gross_Sales”, “NetSales”}),
GroupRows = Table.Group(ExpandColumns, {“QTR_Year”, “ProdNam”, “CntryNam”},
{{“Total Sales”, each List.Sum([NetSales]), type nullable number}}),
ChangeType = Table.TransformColumnTypes(GroupRows,{{“QTR_Year”, type text}, {“ProdNam”, type text}, {“CntryNam”, type text}, {“Total Sales”, type number}})
in
ChangeType
—————————————————————-
The Native Query version for above Power Query:
select [rows].[QTR_Year] as [QTR_Year],
[rows].[ProdNam] as [ProdNam],
[rows].[CntryNam] as [CntryNam],
sum([rows].[NetSales]) as [Total Sales]
from
(
select [$Outer].[QTR_Year],
[$Outer].[ProdNam],
[$Inner].[CntryNam],
[$Inner].[NetSales]
from
(
select [_].[Row_Id],
[_].[FY_Year],
[_].[QTR_Year],
[_].[ProdNam]
from
(
select [Row_Id],
[FY_Year],
[QTR_Year],
[ProdNam]
from
(
Select * From [dbo].[vw_SalesSummary]
) as [$Table]
) as [_]
where [_].[FY_Year] = ‘FY2015’ and [_].[FY_Year] is not null
) as [$Outer]
inner join
(
select [_].[Row_Id] as [Row_Id2],
[_].[FY_Year] as [FY_Year2],
[_].[QTR_Year] as [QTR_Year2],
[_].[CntryNam] as [CntryNam],
[_].[ProdNam] as [ProdNam2],
[_].[Gross_Sales] as [Gross_Sales],
[_].[NetSales] as [NetSales]
from
(
Select * From [dbo].[vw_SalesSummary]
) as [_]
where [_].[FY_Year] = ‘FY2015’ and [_].[FY_Year] is not null
) as [$Inner] on ([$Outer].[Row_Id] = [$Inner].[Row_Id2] or [$Outer].[Row_Id] is null and [$Inner].[Row_Id2] is null)
) as [rows]
group by [QTR_Year],
[ProdNam],
[CntryNam]
Be the first to comment