Power Query using variables from a specific table column value

[Jeffery24]

Hi,

I currently have a table that is adjusted via some variables based off queries. E.g #"Delivery KPI" is a variable.

#"Added Custom30"= Table.AddColumn(#"Added Custom29", "Delivery KPI Target", each #"Delivery KPI"), 

 Works ok but is pretty longwinded as each query variable references the column off another table. Delivery KPI variable is;

= MetricsDelivery[KPI Target]{0}

 

What I'd prefer is to enter the  query variable input directly into the table add column and bypassing the additional query variable

#"Added Custom30"= Table.AddColumn(#"Added Custom29", "Delivery KPI Target", each #"MetricsDelivery[KPI Target]{0}")

Is it possible to reference a table column as a variable directly in a table add column function?

Thanks.

[v-yuezhe-msft · Accepted solution]

@Jeffery24,

In your scenario, firstly, choose the referenced column in the MetricsDelivery table and click drill down, then copy the M code of this query to advanced editor of your second query where  you want to add custom column.

There is an example for your reference.

let
    Source = GoogleAnalytics.Accounts(),
    #"80056532" = Source{[Id="80056532"]}[Data],
    #"UA-80056532-1" = #"80056532"{[Id="UA-80056532-1"]}[Data],
    #"124765743" = #"UA-80056532-1"{[Id="124765743"]}[Data],
    #"Added Items" = Cube.Transform(#"124765743",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:sessionCount", {"ga:sessionCount"}, {"Count of Sessions"}},
            {Cube.AddAndExpandDimensionColumn, "ga:userType", {"ga:userType"}, {"User Type"}},
            {Cube.AddMeasureColumn, "Users", "ga:users"}
        }),//source code of second query



    Source1 = Excel.Workbook(File.Contents("Foldername\testrow.xlsx"), null, true),
    Sheet1_Sheet = Source1{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Emp ID", Int64.Type}, {"Emp Name", type text}, {"Salary", Int64.Type}, {"Email", type text}, {"Geo", type text}}),// source code of first query

    #"Added Custom" = Table.AddColumn(#"Added Items", "Custom", each #"Changed Type"[Emp ID]{0})//reference the first value of Emp ID column in add custom column step of second query
in
    #"Added Custom"

Regard,
Lydia

[Jeffery24]

Hi Lydia,

Thankyou very much, it worked. Took me awhile to get my head around it but will save a lot of work now!

Below is final code, just needed to add {1} to reference next line in table. 

let
    Source = if SourceName = "SBI" then SPDATASBI else SPDATASCOUT,
    #"Merged Queries" = Table.NestedJoin(Source,{"Container Type Source"},#"Container Type",{"Container Type"},"Container Type",JoinKind.LeftOuter),
    #"Expanded Container Type" = Table.ExpandTableColumn(#"Merged Queries", "Container Type", {"Referance", "Max Fill", "Max Weight", "TEU", "Target Fill"}, {"Referance", "Max Fill", "Max Weight", "TEU", "Target Fill"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Container Type",{{"Referance", "Container Type"}, {"TEU", "TEU Set"}, {"Target Fill", "Cont Analysis Target Fill"}}),
    NewType = Table.TransformColumnTypes(#"Renamed Columns1",{{"Cont Analysis Target Fill", Percentage.Type}}),
       
    Source1 = SharePoint.Files(SourceID, [ApiVersion = 15]),
    FileID1 = Source1{[Name=AccountName&" Metrics.xlsx",#"Folder Path"=MyPathParameter]}[Content],
    #"Imported Excel" = Excel.Workbook(FileID1),
    Metrics_Table = #"Imported Excel"{[Item="Metrics",Kind="Table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Metrics_Table,{"In Scope"}),
    MAP = if SourceName = "SBI" then #"SBIMAP" else #"SCOUTMAP",
    #"Merged Queries1" = Table.NestedJoin(#"Removed Columns",{"Recorder Milestone"},MAP,{"Name"},EventMap,JoinKind.LeftOuter),
    ExpandedMAP = Table.ExpandTableColumn(#"Merged Queries1", EventMap, {"PBI Name"}, {"PBI Name"}),
    #"Added Conditional Column" = Table.AddColumn(ExpandedMAP, "Event Measured", each if [PBI Name] = null then [Recorder Milestone] else [PBI Name]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"PBI Name", "Recorder Milestone"}),
    #"Merged Queries2" = Table.NestedJoin(#"Removed Columns1",{"Measurer Milestone"},MAP,{"Name"},EventMap,JoinKind.LeftOuter),
    ExpandedMAP1 = Table.ExpandTableColumn(#"Merged Queries2", EventMap, {"PBI Name"}, {"PBI Name"}),
    #"Added Conditional Column1" = Table.AddColumn(ExpandedMAP1, "Event Measurer", each if [PBI Name] = null then [Measurer Milestone] else [PBI Name]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column1",{"PBI Name", "Measurer Milestone"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"KPI Target", Percentage.Type}, {"Event Measured", type text}, {"Event Measurer", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"#", Order.Ascending}}),
    #"Buffer table" = Table.Buffer(#"Sorted Rows"),
   
    #"Added Custom" = Table.AddColumn(NewType, "Custom", each #"Buffer table"[Event Measured]{0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Record.Field(_,#"Buffer table"[Event Measured]{1}))
 in
    #"Added Custom1"