retrieve value from other table by provideing column name in variable

[Anonymous]

Hi,

I have table 1 like below. Table 1 contains Parameter Name and it's value.

Table 2 :

Table 2 contains limits for all attributes.

I want to retrieve this limits in table 1.

I there any way to configure this dynamically ?

i.e. will take cell content from table i.e. Attribute 1 , name of the site and date  then compare it with table 2 and retrieve respective attribute limit [ attribute 1 limit in row 1 & attribute 2 limit in row 2]

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSXVU0lEy0LMAkoYGQAKODI1AhL6hvpGBkTGQaa5vaABhx+oQoROoHEmrkTlUaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteName = _t, Attribute_1_Limit_1 = _t, Attribute_1_Limit_2 = _t, Attribute_1_Limit_3 = _t, Attribute_2_Limit_1 = _t, Attribute_2_Limit_2 = _t, Attribute_2_Limit_3 = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"Attribute_1_Limit_1", type number}, {"Attribute_1_Limit_2", Int64.Type}, {"Attribute_1_Limit_3", type text}, {"Attribute_2_Limit_1", type text}, {"Attribute_2_Limit_2", type text}, {"Attribute_2_Limit_3", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Attribute_1_Limit_1", "Attribute_1_Limit_2", "Attribute_1_Limit_3", "Attribute_2_Limit_1", "Attribute_2_Limit_2", "Attribute_2_Limit_3"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attributes", "Limits"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","_","",Replacer.ReplaceText,{"Limits"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Limits]), "Limits", "Value")
in
    #"Pivoted Column"

Table1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lEKzixJdQTSjiUlRZlJpSWp8SBRQwMgAUWxOhganFA0GAF5RsgaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Site = _t, Attribute = _t, Value = _t, Limit1 = _t, Limit2 = _t, Limit3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Site", type text}, {"Attribute", type text}, {"Value", Int64.Type}, {"Limit1", type text}, {"Limit2", type text}, {"Limit3", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Site", "Attribute"}, Table2, {"SiteName", "Attributes"}, "Table2", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Limit1", "Limit2", "Limit3"}),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Removed Columns", "Table2", {"Limit1", "Limit2", "Limit3"}, {"Limit1", "Limit2", "Limit3"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table2")
in
    #"Removed Duplicates"

Best Regards