Forum Discussion
Solved
Power Query using variables from a specific table column value
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.
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
2 Replies
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,
LydiaHi 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"
