Forum Discussion
Jeffery24
Helper I
Helper IPower 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 Targ...
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,
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
Jeffery24Hi 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"