Advanced Editor Add Column and Group

[vrajkumar1]

Hello:

I have 2 tables. Table 1 and Table 2.  These two tables are related by a field called UID

Table 1 is an imported table from the server.

Table 2 is sourced from Table 1 with the following query in the Advanced Editor

My goal is this:

1. Populate Table2 with the groupings indicated and 2. add Year(Table1[Service Date]) into Table 2 (there are null values in Table1[Service Date]).  If null in Table1, then null in Table2.  

I tried the below with #Added Custom and  #Grouped Rows

#GroupedRows by itself is working fine.  But a request came in to add Service Date slicer in the visual with Table2 and I was trying to add the column from Table1 and unable to do so.  What am I missing here?

let
Source = Sql.Database("MDCOAP104008", "xxxReporting"),
ibm_Tbl_004_PowerBI_Import = Source{[Schema="ibm",Item="Tbl_004_PowerBI_Import"]}[Data],
ServiceYear = (ibm_Tbl_004_PowerBI_Import[Service Date]),
#"Added Custom" = Table.AddColumn(ibm_Tbl_004_PowerBI_Import,"Service Year",each [ServiceYear]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Provider Type", "Service Facility", "Service Category", "Practice Classification", "CPT/DRG", "Market"},
{{"Charges", each List.Sum([Charges]), type number}, {"Quantity", each List.Sum([quantity]), type number}, {"Medicare Allowable", each List.Sum([Medicare Per Unit]), type number}, {"10th Percentile", each List.Sum([Weighted 10th]), type number}, {"25th Percentile", each List.Sum([Weighted 25th]), type number}, {"Median", each List.Sum([Weighted Median]), type number}, {"75th Percentile", each List.Sum([Weighted 75th]), type number}, {"90th Percentile", each List.Sum([Weighted 90th]), type number}, {"Weight", each List.Sum([Weight]), type number}})
in
#"Grouped Rows"

Thank you for your help

Vadivu.

[vrajkumar1]

This is what I want to achieve:

1. Populate Table2 with the groupings indicated below in my Advanced Editor code and

2. add Year(Table1[Service Date]) into Table 2 (there are null values in Table1[Service Date]).  If null in Table1, then null in Table2.  

This is what I have tried and its not working: (code in Advanced Editor)

let
Source = Sql.Database("MDCOAP104008", "xxxReporting"),
ibm_Tbl_004_PowerBI_Import = Source{[Schema="ibm",Item="Tbl_004_PowerBI_Import"]}[Data],
ServiceYear = (ibm_Tbl_004_PowerBI_Import[Service Date]),
#"Added Custom" = Table.AddColumn(ibm_Tbl_004_PowerBI_Import,"Service Year",each [ServiceYear]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Provider Type", "Service Facility", "Service Category", "Practice Classification", "CPT/DRG", "Market"},
{{"Charges", each List.Sum([Charges]), type number}, {"Quantity", each List.Sum([quantity]), type number}, {"Medicare Allowable", each List.Sum([Medicare Per Unit]), type number}, {"10th Percentile", each List.Sum([Weighted 10th]), type number}, {"25th Percentile", each List.Sum([Weighted 25th]), type number}, {"Median", each List.Sum([Weighted Median]), type number}, {"75th Percentile", each List.Sum([Weighted 75th]), type number}, {"90th Percentile", each List.Sum([Weighted 90th]), type number}, {"Weight", each List.Sum([Weight]), type number}})
in
#"Grouped Rows"

How can I include the column I want to add (Service Date from Table 1) within the #GroupedRows....Or should I have it separate like how I have it above.

[lbendlin]

what is the linking column (the key) between Table 1 and Table 2 ?

[vrajkumar1]

Please see the attached screenshot.  The two tables are joined by UID

[lbendlin]

Then your Table.AddColumn step needs to reflect that.  Pseudo code: 

#"Added Custom" = Table.AddColumn(ibm_Tbl_004_PowerBI_Import,"Service Year",(k)=>Table.SelectRows(Table2,each [UID]=k[UID]),

[vrajkumar1]

The 'k' portion of this is erroring out.  where do I declare what 'k' is?

(k)=>Table.SelectRows(Table2,each [UID]=k[UID]) this seems to be giving me error. 

My Table2 is called 'Summary Table'. 

So I tried - 

Table.AddColumn(ibm_Tbl_004_PowerBI_Import,"Service Year", Table.SelectRows([Summary Table], each [UID] = ibm_Tbl_004_PowerBI_Import[UID]))

and its erroring out.

Thank you for your help.

[lbendlin]

(k) is a placeholder for the current row context.  As you see this is a proper function  (k)=> , not the "each"  substitute.  You cannot mix the two.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

"I was trying to add the column from Table1 and unable to do so"

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?