Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago
Solved

Multiple Intermediate step

ImkeF  came across this awesome link  and I was wondering if there is a way to refer to multiple/any intermediate steps.

 

E.g.

 

// dims table

let
    dimOne = #table({"ky", "valOne"}, {{"ky1", 90.3}, {"ky2", 89.5}}),
    dimTwo = #table({"ky", "valTwo"}, {{"ky1", 1}, {"ky2", 2}}),
    metaStep1 = dimOne meta[ref1=dimOne],
    metaStep2 = dimTwo meta[ref2=dimTwo]
in
    metaStep2

//facts table
let
    fact = #table({"ky", "amt"}, {{"ky1", 100}, {"ky2", 200}}),
    #"Merged Queries" = Table.NestedJoin(fact, {"ky"}, Value.Metadata(dims)[ref2], {"ky"}, "dims", JoinKind.LeftOuter)
in
    #"Merged Queries"

 

 

Is there a way to join metaStep1 in this? The folllowing errors out

 

let
    fact = #table({"ky", "amt"}, {{"ky1", 100}, {"ky2", 200}}),
    #"Merged Queries" = Table.NestedJoin(fact, {"ky"}, Value.Metadata(dims)[ref2], {"ky"}, "dims", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"dims"}),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Columns", {"ky"}, Value.Metadata(dims)[ref1], {"ky"}, "Removed Columns", JoinKind.LeftOuter)
in
    #"Merged Queries1"

 

 

Thank you in advance

AlexisOlson 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Try changing your dims table to this:

    let
    dimOne = #table({"ky", "valOne"}, {{"ky1", 90.3}, {"ky2", 89.5}}),
    dimTwo = #table({"ky", "valTwo"}, {{"ky1", 1}, {"ky2", 2}}),
    metaStep = dimTwo meta[ref1=dimOne, ref2=dimTwo]
in
    metaStep

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    Try changing your dims table to this:

    let
    dimOne = #table({"ky", "valOne"}, {{"ky1", 90.3}, {"ky2", 89.5}}),
    dimTwo = #table({"ky", "valTwo"}, {{"ky1", 1}, {"ky2", 2}}),
    metaStep = dimTwo meta[ref1=dimOne, ref2=dimTwo]
in
    metaStep
    • smpa01's avatar
      smpa01
      Community Champion
      2 years ago

      This does not work in service df

       

      Error: Expression.Error: The field 'ref1' of the record wasn't found
      //DateTbl
let
  Source = {2023..2025},
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  ------------------------------------------------------------
  metaStep =  #"Replace errors" meta[ref1=Source, ref2= #"Replace errors"]
in
 metaStep

//dimTbl
let
  Source = Text.From(Value.Metadata(DateTbl)[ref1]{1}),
  #"Convert to table" = Table.FromValue(Source)
in
  #"Convert to table"