I'm used to Powerquery's sequential command structure where each new step references the output of the previous one, like ...

1. Declare source Table1 with columns a, b, c. [RESULT: a, b, c]
2. Calculate some new field x [RESULT: a, b, c, x]
3. Merge (left join) with Table2 [RESULT: a, b, c, x, T2.y]
4. Group by x [RESULT: agg(a), agg(b), agg(c), x, agg(T2.y)]
5. Merge (left join) with Table3 [RESULT: agg(a), agg(b), agg(c), x, agg(T2.y), T3.z] etc.

I'm having trouble figuring out how to structure a series of transformations like the one above.

I'd appreciate any advice, or links to references or helpful hints out there for someone going from PowerBI to SQL.

CodePudding user response：

There are multiple ways to achieve this. One simple way is

1. Combining steps 1 and 2. Selecting a,b, and c columns, and then calculating a new column iseven.

   select a,b,c, 
   case when b%2 = 0 then 'even' else 'odd' end iseven, 
   from t1
   

2. Left join with t2.

   select a,b,c, 
   case when b%2 = 0 then 'even' else 'odd' end iseven, 
   [t2.y] y
   from t1
   left join t2 
   on t1.a = t2.y
   

3. Using groupby on iseven column and aggregating columns a,b and c. Here we use will wrap the above query in a subquery.

   select sum(a) sumofA, sum(b) sumofB, sum(c) sumofC, Sum(y) sumOfY 
   from
      (
       select a,b,c, 
       case when b%2 = 0 then 'even' else 'odd' end iseven, 
       t2.z y
       from t1
       left join t2 on t1.a = t2.y
       ) master 
    group by iseven
   

4. For the final left join with t3.

   select sumofA,sumofB,sumofC, t3.Z from 
   (
      select sum(a) sumofA, sum(b) sumofB, sum(c) sumofC, Sum(y) 
      sumOfY 
      from
        (
            select a,b,c, case when b%2 = 0 then 'even' else 'odd' end 
            iseven, t2.z y
            from t1
            left join t2 on t1.a = t2.y
         ) master group by iseven
    ) resultTillstep4 
    left join t4 on resultTillstep4.sumofA = t3.Z