locked
Using text from a column to call different user created functions RRS feed

  • Question

  • Dear All,

    Although I have 2 years of M practice I didn't learn how to do this.
    Could someone please, help me?

    I have a table in excel named Companii_Soft

    Soft Cale
    Oblio D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Oblio\Companii.xls
    Saga D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Saga\Companii.xls
    TKR-CF D:\D\Dropbox\C\IET\Financiar\Analize\Surse\CF\IET-CF5.xlsx

    I am queering this with

    let
        Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soft", type text}, {"Cale", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Oblio_fn([Cale]))
    in
        #"Added Custom"

    and i am getting this result

    Soft Cale Custom
    Oblio D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Oblio\Companii.xls [Table]
    Saga D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Saga\Companii.xls
    TKR-CF D:\D\Dropbox\C\IET\Financiar\Analize\Surse\CF\IET-CF5.xlsx


    How can I convert #"Added Custom" step so that it takes every [Soft] it adds "_fn" and takes the [Cale] and returns a [Custom] table.

    So, that is:
    for the Oblio row the step will do #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Oblio_fn([Cale]))
    for the Saga row #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Saga_fn([Cale]))
    for the TKR-CF row #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TKR-CF_fn([Cale]))

    Thank you

    P.S. I am looking for a indirect excel like function.

    Friday, February 23, 2018 5:56 AM

Answers

  • Hi Stevan,

    Since there seems to be a fixed number of these functions and they're created by hand, could you just add a custom column that does the following?

    = if [Soft] = "Oblio" then Oblio_fn([Cale]) else if [Soft] = "Saga" then Saga_fn([Cale]) else ...

    Or create a function:

    (soft as text, cale as text) => if soft = "Oblio" then Oblio_fn(cale) else if soft = "Saga" then Saga_fn(cale) else ...

    And add a custom column that calls it:

    = MyCustomFunc([Soft], [Cale])

    Ehren


    Friday, March 9, 2018 12:44 AM

All replies

  • So you have multiple functions for each "Soft"?

    This could be handled like this then: Function.Invoke([Soft]&"_fn", { [Cale] })

    This allows you to reference an object (function) by a Text string.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 23, 2018 6:10 AM
  • Thank you Imke,

    Yes, I have multiple functions, correctly named.

    Changing last M line from

    = Table.AddColumn(#"Changed Type", "Custom", each Oblio_fn([Cale]))

    into

    = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke([Soft]&"_fn", { [Cale] }))

    would get me to this error (for the first row):

    Expression.Error: We cannot convert the value "Oblio_fn" to type Function.
    Details:
        Value=Oblio_fn
        Type=Type

    Thank you again for help and time.

    P.S. I have also tried

    = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke(Function.From([Soft]&"_fn",{ [Cale] })) )

    which gets me to the error

    Expression.Error: We cannot convert the value "Oblio_fn" to type Type.
    Details:
        Value=Oblio_fn
        Type=Type

    • Edited by Stevan Dinu Friday, February 23, 2018 8:37 AM
    Friday, February 23, 2018 8:22 AM
  • Sorry, forgot that this method just works for the function arguments "out of the box". When the function itself shall be passed as a text-string, you have to create a record first:

    FunctionRecord = [Function1 = Function1, Function2 = Function2],

    AddColumnStep = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke(Record.Field(FunctionRecord,[Soft])&"_fn", { [Cale] }))

    Where you take the function/query names as names for the record fields. That way you can use the same string for the selection.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 23, 2018 8:39 AM
  • Thanks again Imke,

    I've tried various combinations (all with error).
    Here is the full code with your last suggestion.

    let
        Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soft", type text}, {"Cale", type text}}),
        FunctionRecord = [Function1 = Function1, Function2 = Function2],
        //FunctionRecord = [Oblio_fn = Function1, Saga_fn = Function2],
        //FunctionRecord = [Function = each  [Soft])&"_fn"],
        //FunctionRecord = [Function1 = Oblio_fn, Function2 = Saga_fn],
        acc = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke(Record.Field(FunctionRecord, [Soft]) & "_fn", { [Cale] }))
    in
        acc 

    The error in the fist line is

    Expression.Error: The field 'Oblio' of the record wasn't found.
    Details:
        Function1=Error
        Function2=Error

    However, I tried 
    Record.Field(FunctionRecord, [Soft] & "_fn") instead of your suggested 
    Record.Field(FunctionRecord, [Soft]) & "_fn" but no luck.

    Thank you again for you time and help.

    Friday, February 23, 2018 9:36 AM
  • There is an issue with the closing bracket in your formula:

    let
        Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soft", type text}, {"Cale", type text}}),
        FunctionRecord = [Function1 = Function1, Function2 = Function2],
        //FunctionRecord = [Oblio_fn = Function1, Saga_fn = Function2],
        //FunctionRecord = [Function = each  [Soft])&"_fn"],
        //FunctionRecord = [Function1 = Oblio_fn, Function2 = Saga_fn],
        acc = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke(Record.Field(FunctionRecord, [Soft] & "_fn"), { [Cale] }))
    in
        acc 

    It mustn't come after [Soft], but after "_fn"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Friday, February 23, 2018 9:44 AM
  • FunctionRecord gets this error

    Expression.Error: The name 'Function1' wasn't recognized.  Make sure it's spelled correctly.

    Friday, February 23, 2018 9:53 AM
  • The record must contain your function names. So probably sth like:

    let
         Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
         #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soft", type text}, {"Cale", type text}}),
         FunctionRecord = [Oblio_fn = Oblio_fn, Saga_fn = Saga_fn],
         acc = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke(Record.Field(FunctionRecord, [Soft] &"_fn"), { [Cale] }))
     in
         acc 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Friday, February 23, 2018 9:56 AM
  • Thanks,

    Now it is working.

    Is there a way to autofill the FunctionRecord with all the [Soft] row and adding  "_fn"?

    Thank you

    Friday, February 23, 2018 9:59 AM
  • Theoretically (codewise), that's possible. But unfortunately this doesn't run stable. I've seen issues with this when loading the results to the datamodel in Power BI. But feel free to try it out:

    Record.SelectFields(#shared, List.Transform(#"Changed Type"[Soft], each _&"_fn"))


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 23, 2018 11:02 AM
  • A more reliable way might be to consolidate all your different functions into one with the value from [Soft] as an additional parameter that determines which (sub)-function will actually be returned.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 23, 2018 11:10 AM
  • I have tried the more reliable solution you suggested.

    But I cannot find a way to Go To (VBA), or Select Case (VBA) to just jump to "subfunction".

    Can you please, suggest one?

    Other than Repicate SWITH from
    Miguel https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/#
    or Ken's 
    https://www.excelguru.ca/blog/2014/09/03/multi-condition-logic-in-power-query/

    Thank you

    SWITCH

    Friday, February 23, 2018 11:51 AM

  • Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 23, 2018 12:09 PM
  • Hy and thanks for your usefull sugestions.

    I cannot find a way to convert a Switch stetement into a multi-subfunction routine.

    Can you help me, please?

    Sunday, February 25, 2018 6:11 AM
  • Hi Stevan,

    please check this query:

    let
    
    //1 MSwitch Function
        MSwitch =  
             (Expression as any, Values as list, Results as list, optional Else as any) =>
                    try Results{List.PositionOf(Values, Expression)} 
                    otherwise if Else = null then "Value not found" 
                                             else Else,
    
    //2 Function Parameter (Name) for Switch Function 
        FunctionName= "FunctionA",
    
    //3 Functions - they normally sit in your query pane
        FunctionA = (x) => x*10,
        FunctionB = (x)=>x*20,
    
    //4 Call the Switch function: This chooses which (inner) function will be chosen and returns that function
        CallSwitch = MSwitch(FunctionName, {"FunctionA", "FunctionB"}, {FunctionA, FunctionB})
    
    in
        CallSwitch

    In step 4 ("CallSwitch") the first parameter is the name of the chosen function (your [Soft]&"_fn"), the second is the list of the function names (!! See the quotes !!), and the third parameter is a list of the functions itself (without quotes: Now they are variables and represent the functions themselves).

    The result of this step is a function (the selected one).

    I've prepared a file where you can see this in action: https://1drv.ms/u/s!Av_aAl3fXRbehbFLyExwsN5bq93isA


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 25, 2018 6:53 AM
  • Is there a why something like the following wouldn't work (based on the original post)?

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Expression.Evaluate([Soft] & "_fn("""&[Cale]&""")", #shared))
    

    Sunday, February 25, 2018 10:19 PM
  • I was trying to post a reply with pictures since 2 days ago.
    I am still unable, since I was not verified.
    If anyone can help... please do.

    However (a quick review):

    I have this Companii_Soft table

    Soft Cale
    Oblio D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Oblio\Companii.xls
    Saga D:\D\Dropbox\C\IET\Financiar\Analize\Surse\Saga\Companii.xls
    TKR_CF D:\D\Dropbox\C\IET\Financiar\Analize\Surse\CF\IET-CF5.xlsx


    And I have this (working) code

    let
        Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
        //Next 3 Lines sugested by Imke Feldmann at https://social.technet.microsoft.com/Forums/en-US/9c4364a7-9867-4f2f-ba11-7774b3f66366/using-text-from-a-column-to-call-different-user-created-functions?forum=powerquery
        FunctionRecord = [Oblio = Oblio_fn, Saga  = Saga_fn, TKR_CF = TKR_CF_fn],
        //FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Soft], each _ &"_fn")),
        acc = Table.AddColumn(Source, "Custom", each Function.Invoke(Record.Field(FunctionRecord, [Soft]), { [Cale] })),
        ct = Table.Combine(acc[Custom])
    
    in
        ct

    Working FunctionRecord = [Oblio = Oblio_fn, Saga  = Saga_fn, TKR_CF = TKR_CF_fn],
    returns this result:
    Oblio Function
    Saga Function
    TKR_CF Function.

    The only disadvantage of this line, is that I should mention all by hand.
    Of course, the next line will be better.

    But, //FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Soft], each _ &"_fn")),
    returns this result:
    Oblio_fn Function
    Saga_fn Function
    TKR_CF_fn Function

    Getting this error:

    Expression.Error: The field 'Oblio' of the record wasn't found.
    Details:
        Oblio_fn=Function
        Saga_fn=Function
        TKR_CF_fn=Function

    How should I modify the non-working //FunctionRecord to return the presented, working result.

    Thank you

    Monday, February 26, 2018 8:35 AM
  • The problem is that you are referencing the field names with _fn suffix rather than the "naked" field names. Here is a possible fix 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        //FunctionRecord = [Oblio = Oblio_fn, Saga  = Saga_fn, TKR_CF = TKR_CF_fn],
        FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Soft], each _ &"_fn")),
        acc = Table.AddColumn(Source, "Custom", each Record.Field(FunctionRecord, [Soft] &"_fn")([Cale])),
        ct = Table.Combine(acc[Custom])
    
    in
        ct


    • Edited by Igor Cotruta Monday, February 26, 2018 4:21 PM correction on terminology
    Monday, February 26, 2018 2:51 PM
  • Thank yoy Colin,

    I've tried this line (you sugested)

    acc = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Soft] & "_fn("""& [Cale] &""")", #shared)), //Colin Banfield

    in the curated code i provided on Monday, February 26, 2018 8:35 AM.

    But it gets to this error (for Oblio line):

    Expression.Error: [1,1-1,9] The name 'Oblio_fn' doesn't exist in the current context.
    Details:
        List

    Every line in Custom column gets error.


    • Edited by Stevan Dinu Monday, March 5, 2018 3:18 PM Code Inserted as Block
    Monday, March 5, 2018 3:03 PM
  • Thank you Igor,

    My intent is to auto-generate the FunctionRecord step (based on values in rows).
    See //How to make this next line working? down in the code.

    Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
    		//Next 3 Lines sugested by Imke Feldmann at https://social.technet.microsoft.com/Forums/en-US/9c4364a7-9867-4f2f-ba11-7774b3f66366/using-text-from-a-column-to-call-different-user-created-functions?forum=powerquery
    		FunctionRecord = [Oblio = Oblio_fn, Saga  = Saga_fn, TKR_CF = TKR_CF_fn],
    		//How to make this next line working?
    //FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Soft], each _ &"_fn")), //Imke Feldmann notWorking acc = Table.AddColumn(Source, "Custom", each Record.Field(FunctionRecord, [Soft] &"_fn")([Cale])), //Igor Cotruta notWorking //acc = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Soft] & "_fn("""& [Cale] &""")", #shared)), //Colin Banfield notWorking //acc = Table.AddColumn(Source, "Custom", each Function.Invoke(Record.Field(FunctionRecord, [Soft]), { [Cale] })), //Imke Feldmann


    Also the line you sugested returns this error:

    Expression.Error: The field 'Oblio_fn' of the record wasn't found.
    Details:
        Oblio=Function
        Saga=Function
        TKR_CF=Function

    thank you


    Monday, March 5, 2018 3:17 PM
  • Thank yoy Colin,

    I've tried this line (you sugested)

    acc = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Soft] & "_fn("""& [Cale] &""")", #shared)), //Colin Banfield

    in the curated code i provided on Monday, February 26, 2018 8:35 AM.

    But it gets to this error (for Oblio line):

    Expression.Error: [1,1-1,9] The name 'Oblio_fn' doesn't exist in the current context.
    Details:
        List

    Every line in Custom column gets error.


    Hi Stevan,

    My assumption was that your functions 'Oblio_fn, Saga_fn, etc., were queries in your Excel workbook (or Power BI file). If they are not, then my suggestion won't work...which brings up the question - where are these functions defined?

    Monday, March 5, 2018 3:30 PM
  • Hi 

    I'm pretty sure I've tested my code.

    let
        Source = Excel.CurrentWorkbook(){[Name="Companii_Soft"]}[Content],
        //FunctionRecord = [Oblio = Oblio_fn, Saga  = Saga_fn, TKR_CF = TKR_CF_fn],
        FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Soft], each _ &"_fn")),
        acc = Table.AddColumn(Source, "Custom", each Record.Field(FunctionRecord, [Soft] &"_fn")([Cale])),
        ct = Table.Combine(acc[Custom])
    
    in
        ct

    Here is it again with table name renamed to match yours.

    These functions I made them with the dummy code ie.

    Oblio_fn = (x)=>x

    Monday, March 5, 2018 4:08 PM
  • Hello,

    Just FYI. The Expression.Evaluate option does work.

     N -



    Monday, March 5, 2018 4:45 PM
  • Hello,

    Just FYI. The Expression.Evaluate option does work.

     N -




    When I tested the Expression.Evaluate solution (before posting), a result was returned for Oblio_fn, and the same error that Stevan mentioned was returned for the other functions. The errors occurred because I had not defined function queries for Saga_fn and TKR-CF_fn, so obviously the names couldn't be found. It's the only reason why Stevan's observed behavior should occur. Hopefully, he is aware that names are case sensitive. 
    Monday, March 5, 2018 8:12 PM
  • I think that auto-generation of list of functions from #shared record isn't stable because of lazy evaluation. When working with #shared in Query Editor, we load a preview, so these functions are evaluated to the theirs function values. Even after the first load to worksheet, we can see these values.

    But if we try to refresh, we'll get errors - because these functions are not evaluated (there are no such functions in #shared when we evaluate main query! not even promises of functions, as far as I can see) and are not visible then.

    I cannot describe it better, may be, Curt can. 

    PS. Thats why all my attempts to make CALENDARAUTO in PQ failed :)


    Maxim Zelensky Excel Inside

    Wednesday, March 7, 2018 10:24 AM
  • I think that auto-generation of list of functions from #shared record isn't stable because of lazy evaluation. When working with #shared in Query Editor, we load a preview, so these functions are evaluated to the theirs function values. Even after the first load to worksheet, we can see these values.

    But if we try to refresh, we'll get errors - because these functions are not evaluated (there are no such functions in #shared when we evaluate main query! not even promises of functions, as far as I can see) and are not visible then.

    I cannot describe it better, may be, Curt can. 

    PS. Thats why all my attempts to make CALENDARAUTO in PQ failed :)


    Maxim Zelensky Excel Inside

    Hi Maxim,

    I'm not sure where auto-generation comes into the picture. Each function query (Oblio_fn, Saga_fn, etc.) has been created manually in the query editor. Each function returns a workbook as a table in a custom column of the original table(Companii_Soft). The expression in the custom column uses Expression.Evaluate, as previously described. It works 100% reliability  over multiple refreshes. 

    There's obviously something different in Stevan's workbook, so unless the actual workbook is available to look at, there's nothing more that I can suggest.

    Wednesday, March 7, 2018 10:45 PM
  • Hi Stevan,

    Since there seems to be a fixed number of these functions and they're created by hand, could you just add a custom column that does the following?

    = if [Soft] = "Oblio" then Oblio_fn([Cale]) else if [Soft] = "Saga" then Saga_fn([Cale]) else ...

    Or create a function:

    (soft as text, cale as text) => if soft = "Oblio" then Oblio_fn(cale) else if soft = "Saga" then Saga_fn(cale) else ...

    And add a custom column that calls it:

    = MyCustomFunc([Soft], [Cale])

    Ehren


    Friday, March 9, 2018 12:44 AM
  • Hi Colin,

    hmm... I do not sure where I'm wrong.

    I defined 3 functions A_fn, B_fn, C_fn and a query - one step adds column Igor's way, and one adds column in your's way:

    // A_fn
    let
        Source = (optional x as text)=>Text.Combine({x, Text.NewGuid()}, ", ")
    in
        Source
    
    // B_fn
    let
        Source = (optional x as text)=>Text.Combine({x, Text.NewGuid()}, ", ")
    in
        Source
    
    // C_fn
    let
        Source = (optional x as text)=>Text.Combine({x, Text.NewGuid()}, ", ")
    in
        Source
    
    // Query1
    let
        Source = #table(type table [#"Name"=text],{{"A"},{"B"},{"C"}}),
        FunctionRecord = Record.SelectFields(#shared, List.Transform(Source[Name], each _ &"_fn")),
        Igor = Table.AddColumn(Source, "Igor", each Record.Field(FunctionRecord, [Name] &"_fn")([Name]), type text),
        Colin = Table.AddColumn(Igor, "Colin", each Expression.Evaluate([Name] & "_fn("""&[Name]&""")", #shared), type text)
    in
        Colin

    Both codes works very well in Query Editor window.

    Wen I try to load the result on the worksheet:

    after the 2nd refresh, I got this:

    you can look into the workbook there, or recreate it yourself


    Maxim Zelensky Excel Inside


    Monday, March 12, 2018 1:33 PM
  • Hi Maxim,

    That fact that the data in all of the columns disappear after returning the table to Excel seems like some kind of bug to me. If Text.Guid is first memoized in your functions, i.e.

    let
        NewGuid = Text.NewGuid(),
        Source = (optional x as text)=>Text.Combine({x, NewGuid}, ", ")
    in
        Source

    the query works perfectly.

    At any rate, the problem that Stevan is experiencing is much more fundamental in that he is getting an error that "The name 'Oblio_fn' doesn't exist in the current context." So barring the anomaly you discovered with using Text.Guid, you've demonstrated that the technique (aptly applied) doesn't generate that error.

    Monday, March 12, 2018 2:25 PM
  • Hi Colin.

    I cannot reproduce your idea. I changed all functions in my XLSX to your code, and... nothing changes, I've got the same result. 

    I changed one of the functions to A_fn = (x)=>x (the same result)

    Moreover, the same behavior is in Power BI (there you got an error immediately after the query load & apply).

    > "The name 'Oblio_fn' doesn't exist in the current context."

    I can reproduce this error when I use the next code: 

    // Query2
    let
        Source = Expression.Evaluate("A_fn(""got it"")", #shared)
    in
        Source

    Loading this query to the worksheet I immediately got an error:

    [Expression.Error] [1,1-1,5] The name "A_fn" doesn't exist in the current context.

    In Power BI error description is more... descriptive: "OLE DB or ODBC error: [Expression.Error] The name "A_fn" doesn't exist in the current context".

    I think it is the another one "by (lazy evaluation) design". Query1 doesnt directly call the functions, so there is no query dependencies, SO, when Query1 evaluated, there is no A_fn and other such custom functions in the current evaluation context (#shared).

    #shared always has the Library items, but custom items (from Section1 etc.) being added there only when there is a need for them in current calsulations, i.e. there is a query dependence. I understand the logics of developers (if I got it right, of course) - why evaluate queries that we do not need to use when calculating the final result.

    But, being on the other side of M developers, I'd prefer that these indirect references to be evaluated. And confusing behavior of preview in query editor also has to be fixed.


    Maxim Zelensky Excel Inside


    Monday, March 12, 2018 3:27 PM
  • Weird...I can't get the query to fail (even your own workbook, after making the change to Text.NewGuid), and you can't get it to work!? I replicated the query and functions in Power BI and it works perfectly. Need to investigate further. I've tried changing a couple of settings - Fast Data Load & Privacy, but those changes are not making any difference.

    Edit:

    "#shared always has the Library items, but custom items (from Section1 etc.) being added there only when there is a need for them in current calsulations, i.e. there is a query dependence."

    I have never observed that being the case. On the contrary, if I enter = #shared in a blank query, all of the queries that I created in the editor show up in the shared list, unless I'm mistaking your meaning. However, your explanation can't explain why I can't get this thing to fail.

    Monday, March 12, 2018 3:56 PM
  • On the contrary, if I enter = #shared in a blank query, all of the queries that I created in the editor show up in the shared list, unless I'm mistaking your meaning.

    Add 3 queries to the new report, not linked to each other. Something simple, like 

    let
        Source = 1
    in
        Source

    Then make a NewQuery: 

    let
        Source = #shared,
        Count = Record.FieldCount(Source)
    in
        Count

    on the first step in query editor you can see the all 4 custom queries. Remember the result of this query (suppose it is 822). Then in load it to the worksheet (or to Power BI model and create the Card visual). You will see 819 (-3), because when NewQuery is calculated there are no other queries that ought to be evaluated (and they are not in #shared). No link - No evaluation - No query materialized.


    Maxim Zelensky Excel Inside

    Friday, March 16, 2018 3:49 PM
  • Hi Maxim,

    I get the same count (740), in the query editor and in the returned table. In addition, instead of returning the count, I converted the shared record to a table in NewQuery, and returned the table to Excel. The new queries show up at the top.

    Friday, March 16, 2018 6:36 PM
  • Hi Colin,

    I thought I gone crazy, but then I understood what is the reason of such different behavior.

    You are using Excel 2016, aren't you? When I tried to repeat this in Excel 2013 with Power Query Add-In, I failed.

    Now on the another PC I tried to repeat this, and everything works as you described. I think this is the difference between Add-In and built-in Power Query. At least, now I can see it in action. I do not know, is this difference is an improvement in 2016, or an improvement in add-in (I think former), but this 

    But, for Power BI (March 2018, Store version)  I cannot do the same as in Excel 2016. It looks the same as in Add-In for 2013. You can see it there https://youtu.be/Dk-QyCereL0 (less than 4 minutes, no sound)

    But anyway, this is very interesting case.


    Maxim Zelensky Excel Inside

    Friday, March 16, 2018 11:33 PM
  • Hey Maxim,

    Checked your video. The reason for count differences is that the Queries 1 X don't make it into the cube as data sources (tables), that's why #shared is slightly different from the preview version.

    Saturday, March 17, 2018 2:29 PM
  • Maxim,

    After my last post, I had similar thoughts about Excel 2016 (which I am using), versus the PQ add-in, but couldn't reconcile the behavior of Power BI. In Power BI, you can see the difference immediately after loading the data to the data model.

    Igor,

    "The reason for count differences is that the Queries 1 X don't make it into the cube as data sources (tables)"

    Query 1 X? Cubes? It's not clear what your statement means. Could you elaborate, or it least restate using more understandable terms?

    Saturday, March 17, 2018 5:04 PM
  • Igor,

    but functions that in Queries 1 X are not data sources like tables... And they are not visible after refresh in Excel 2013 with Power Query add-in (latest add-in version for today - 2.54.4970.321)

    So there 3 different behavior patterns:

    Excel 2016 - view all queries in #shared after loading to worksheet AND after refresh table data source.

    Add-in - view all queries in #shared after loading to worksheet, but NOT after refresh of loaded table.

    Power BI - view all queries in query editor only, and queries with "Enable load" in data model.

    Colin,

    I think Igor mean that in my video there are three functions named "Query1", "Query1 (2)", "Query1 (3)" - that's why "Query1 X"


    Maxim Zelensky Excel Inside

    Monday, March 19, 2018 8:16 AM
  • I think Igor mean that in my video there are three functions named "Query1", "Query1 (2)", "Query1 (3)" - that's why "Query1 X".

    Du-uh!

    Monday, March 19, 2018 5:19 PM
  • Colin

    Elaborating on "..don't make it into the cube as data sources (tables).."

    The queries created in Query Editor pane (Preview) weren't created as data sources in the embedded tabular model of Power BI. Most likely because they don't have any output. That is why they don't show up as tables in the Data view. And since the tabular model has its own view of #shared  it misses a few query definitions, and shows a different count in the report vs the Query Editor.

    Wednesday, March 21, 2018 1:39 PM
  • Hi Stevan,

    have you been able to solve your issue? Then please mark the suitable one(s) as answers. Thanks!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, April 15, 2018 7:55 AM