The Great Function Project Part 3

Lord of the Rings. Mad Max. Star Wars. Indiana Jones… The Great Function Project.

Another great trilogy comes to an end… well at least until the next one comes out.

If you haven’t “seen” part 1 & part 2, go back and check those out in sequence. We’re two-thirds of the way through a co-development project to build a complete reference of every DAX, Excel & M function available to you. In part 3 today, we’re finishing it out by adding the NKOTB into the mix – M.

Yeah, M has its challenges when you’re starting out with it… Search Engine utilization being just one of them. We’ll resolve that one today so that you’ll never need to search for an M function again.

Lack of Intellisense when writing M is another major one. Just like DAX and Excel, there is an expected syntax to M, but Power BI is not going to help you out with this – yet. We’re also going to improve that situation too by getting you definitions and examples of many of the functions.

Sounds good? Let’s go.
So click Edit Queries to enter the Power Query Editor, and we’ll get started.

M Lives Here

The M Formula Bar is disabled by default, so make sure you have it enabled. If you don’t see it go to the View menu, then check the box on the left that says Formula Bar.

M Formula Bar is disabled

Go to New Source –> Blank Query

Go to New Source - Blank Query

Not all M is going to be this easy, but this one will be a good confidence builder Winking smileGo to the Formula bar and tweet write:

#shared

Yep, that’s it. One case-sensitive word gets you the complete library of M Functions – and some extras we’re not interested in right now.

Complete library of M Functions

A list of names isn’t enough you say? Well, aren’t you getting a little spoiled now? OK, OK. We can do this, but to get the rest of the metadata that I know you want, that will mean writing a few custom M columns and doing a little cleanup. It’s about a 10 step process, so for the sake of time, we’ll take a shortcut and you can reverse engineer the details of this query once we’re done (spoiler: Value.Metadata is the magic function).

Go to Home –> Advanced Editor

Go to Home - Advanced Editor

Then copy the below code over whatever you have appearing in the Advanced Editor window then hit OK.

let
Source = #shared,
#”Converted List to Table” = Record.ToTable(Source),
#”Added Type Column” = Table.AddColumn(#”Converted List to Table”, “ValueType”, each Value.Type([Value])),
#”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function?”, each Type.Is([ValueType], type function)),
#”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
#”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),
#”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
#”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
#”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
#”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
#”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
#”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
#”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
#”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,”<code>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”</code>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,”<ul>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value3″ = Table.ReplaceValue(#”Replaced Value2″,”<li>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,”</ul>”,””,Replacer.ReplaceText,{“Long Description”}),
#”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,”</li>”,””,Replacer.ReplaceText,{“Long Description”})
in
#”Replaced Value5″

And now you have a complete listing of 674 M functions along with a decent amount of the associated metadata! There are a total of 12 M functions being used in this query (highlighted yellow) and you now have the resource to take a closer look at what they do.

P3 M Drillthrough

Bringing It All Together (Optional Exercise in Futility)

We’ve already created a lookup table to cross-reference DAX & Excel in Part 1. There’s no reason why we can’t add M to that lookup table, even though there is zero overlap.

You’re probably going to run into errors at this step or on the refresh of the Function Lookup Table. The DAX query from part 1 is going to be the culprit. You’ll need to re-run DAX Studio and update the DAX query Source step with both the new local host and new catalog_name fields following the steps from part one here or here.

To update your Function Lookup Table with the M functions.

1 – Select the Function Lookup Query you created in Part 2
2 – Click the gear icon on step 2 of the query ‘Appended Query’
3 – Change the default Append Query behavior from appending ‘Two tables’ to ‘Three or More tables.’
4 – Select the M table
5 – Click Add to now combine DAX, Excel & M

Final Steps - Append Query

And we’re done. Hit close and apply and proceed to layout your complete Power BI & Excel reference in any way that works for you!

Let’s recap what WE covered in the Great Function Project:

We queried the metadata of a PBIX file (twice) even writing a little T-SQL along the way.

We used DAX Studio.

We connected to SQL Server Analysis Services.

We built a web scraping application.

We performed some ETL – indirectly writing M in the process.

We created a dynamic lookup table driven my 3 separate fact tables.

We have written M and we’ve also “borrowed” some, both of which are fine.

….and you have done this using a free application with no traditional data sources in (hopefully) less than 30 minutes total!

As much as this project was building up to the end product, we sure did get some reps in a lot of different techniques along the way! If any of this is new to you, I hope it’s demystified a few things for you.

Please download the .pbix for your reference here

I’d also LOVE to hear how you put your workbook to use and would love to see what you’ve put together. Feel free to share it with me [email protected]

Did you find this article easier to understand than the average “tech” article?

We like to think that is no accident.  We’re different.  First of a new breed – the kind who can speak tech, biz, and human all at the same time.

Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!

  Subscribe to PowerPivotPro!
X

Subscribe

Mat Herring

Mat is a US CPA and Australian Chartered Accountant with 15 years’ experience across global financial services giants and bootstrapping startups alike. During that period, the Microsoft Power Suite became the secret weapon that allowed Mat to transition from competent finance manager to cross-functional CFO. A real life example of the career empowerment that can take place when a bean counter gets hands on with the analytics. 

This Post Has 23 Comments

  1. It did not work when I use “#shared” I had to add “=” for a code of =#shared. I am a very literal person. 🙂

    1. Try this:

      let
      Source = #shared,
      #”Converted List to Table” = Record.ToTable(Source),
      #”Added Type Column” = Table.AddColumn(#”Converted List to Table”, “ValueType”, each Value.Type([Value])),
      #”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function?”, each Type.Is([ValueType], type function)),
      #”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
      #”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),

      #”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
      #”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
      #”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
      #”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
      #”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
      #”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
      #”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
      #”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,”","",Replacer.ReplaceText,{"Long Description"}),
      #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","
      “,””,Replacer.ReplaceText,{“Long Description”}),
      #”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,””,””,Replacer.ReplaceText,{“Long Description”}),
      #”Replaced Value3″ = Table.ReplaceValue(#”Replaced Value2″,””,””,Replacer.ReplaceText,{“Long Description”}),
      #”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,””,””,Replacer.ReplaceText,{“Long Description”}),
      #”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,””,””,Replacer.ReplaceText,{“Long Description”})

      in
      #”Replaced Value5″

      1. I am still receiving errors. That being said, I reworked the query by adding ever single line by hand and using the UI where possible. I worked around the no columns found error by loading more data. Now that I have a “functional” dysfunctional query, it refuses to allow itself to be added the the look up table by saying a step in replaced value 5 interferes with a step in the original DAX table. My current end result is that I have the query functioning (not perfect as I had to add extra steps and complete it manually) but it isn’t playing nice with the others. I don’t know why it flagged so many innocuous things.

        1. Probably because it changes the quotation marks for HTML. Download the model that Reid provides and then look at the coding.

          1. I changed all of the quotations. I also tried Reid’s model. His file says it has functions that are not available for release yet but other than that, I keep getting errors. Now I only get them on the append step. The new error message is: Function Lookup Table: OLE DB or ODBC error: Query ‘M’ (step ‘Replaced Value5’) is referencing Query ‘DAX’ (step ‘Renamed Columns1’, which was not part of its formula text. Here are my Queries:
            M —
            let
            Source = #shared,
            #”Converted to Table” = Record.ToTable(Source),
            #”Added Type Column” = Table.AddColumn(#”Converted to Table”, “ValueType”, each Value.Type([Value])),
            #”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function”, each Type.Is([ValueType], type function)),
            #”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
            #”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),
            #”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
            #”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
            #”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
            #”Removed Errors” = Table.RemoveRowsWithErrors(#”Renamed Columns”, {“Is Function”}),
            #”Filter out non-functions” = Table.SelectRows(#”Removed Errors”, each ([Is Function] = true)),
            #”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Is Function”, “Documentation.Examples”}),
            #”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”)),
            #”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”)),
            #”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,”","",Replacer.ReplaceText,{"Long Description"}),
            #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","
            “,””,Replacer.ReplaceText,{“Long Description”}),
            #”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,””,””,Replacer.ReplaceText,{“Long Description”}),
            #”Replaced Value3″ = Table.ReplaceValue(#”Replaced Value2″,””,””,Replacer.ReplaceText,{“Long Description”}),
            #”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,””,””,Replacer.ReplaceText,{“Long Description”}),
            #”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,””,””,Replacer.ReplaceText,{“Long Description”})
            in
            #”Replaced Value5″

            Dax —
            let
            Source = AnalysisServices.Database(“LOCALHOST:63651”, “06f64dba-e2c0-4571-867f-bb3da920b16d”, [Query=”select [FUNCTION_NAME], [DESCRIPTION], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $SYSTEM.MDSCHEMA_FUNCTIONS”]),
            #”Filtered Rows” = Table.SelectRows(Source, each ([ORIGIN] 1)),
            #”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“FUNCTION_NAME”, “Function name”}, {“INTERFACE_NAME”, “Group”}}),
            #”Removed Duplicates” = Table.Distinct(#”Renamed Columns”, {“Function name”}),
            #”Uppercased Text” = Table.TransformColumns(#”Removed Duplicates”,{{“Function name”, Text.Upper, type text}}),
            #”Trimmed Text” = Table.TransformColumns(#”Uppercased Text”,{{“Function name”, Text.Trim, type text}}),
            #”Cleaned Text” = Table.TransformColumns(#”Trimmed Text”,{{“Function name”, Text.Clean, type text}}),
            #”Renamed Columns1″ = Table.RenameColumns(#”Cleaned Text”,{{“DESCRIPTION”, “DAX Function Description”}, {“Group”, “Function Group”}})
            in
            #”Renamed Columns1″

            and Function Lookup Table —
            let
            Source = DAX,
            #”Appended Query” = Table.Combine({Source, Excel, M}),
            #”Removed Other Columns” = Table.SelectColumns(#”Appended Query”,{“Function name”}),
            #”Removed Duplicates” = Table.Distinct(#”Removed Other Columns”)
            in
            #”Removed Duplicates”

          2. I had to do the same. Cut the M-code to notebook and replaced all the HTML “” with the keyboard “”. Worked perfectly.
            I also scraped some SYNTAX references from:
            = Web.Page(Web.Contents(“http://www.powerpivot-info.com/post/52-list-of-powerpivot-dax-functions-with-description”))
            Eight pages in all and combined these into a DAX ALL table with a Syntax guide where they were available.
            So for example, CALCULATE Expands to show FunctionSyntax of CALCULATE(,,…)

            I then added a card in my visual with the following formula.
            DAX Function = IF(COUNTROWS(‘DAX’) >1,””, COMBINEVALUES( ” -: “, MIN(‘DAX'[Function Name]), MIN([DAX Function Description]), “SYNTAX”, MIN(‘DAX'[Function Syntax])))

            Therefore when a DAX function is selected, the DAX Function name displays + Dax Description + Syntax example if available. If more than one item is displayed, then the card is blank.


      2. let
        Source = #shared,
        #”Converted List to Table” = Record.ToTable(Source),
        #”Added Type Column” = Table.AddColumn(#”Converted List to Table”, “ValueType”, each Value.Type([Value])),
        #”Is this row a function?” = Table.AddColumn(#”Added Type Column”, “Is Function?”, each Type.Is([ValueType], type function)),
        #”METADATA!” = Table.AddColumn(#”Is this row a function?”, “GoodStuff”, each Value.Metadata([ValueType])),
        #”Show me the Metadata” = Table.ExpandRecordColumn(#”METADATA!”, “GoodStuff”, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}, {“Documentation.Name”, “Documentation.Description”, “Documentation.LongDescription”, “Documentation.Category”, “Documentation.Examples”, “Documentation.DisplayName”, “Documentation.Caption”}),

        #”If there is more than one example take the first one” = Table.AddColumn(#”Show me the Metadata”, “Examples”, each try if Type.Is(Value.Type([Documentation.Examples]), type record) then [Documentation.Examples] else List.First([Documentation.Examples]) otherwise null),
        #”Show me the examples” = Table.ExpandRecordColumn(#”If there is more than one example take the first one”, “Examples”, {“Description”, “Code”, “Result”}, {“Description”, “Code”, “Result”}),
        #”Renamed Columns” = Table.RenameColumns(#”Show me the examples”,{{“Name”, “Function name”}, {“Code”, “Example Code”}, {“Result”, “Example Output”}, {“Documentation.Description”, “Short Description”}, {“Documentation.LongDescription”, “Long Description”}, {“Documentation.Name”, “Documentation Name”}, {“Documentation.Category”, “Category”}, {“Documentation.DisplayName”, “Documentation DisplayName”}, {“Documentation.Caption”, “Documentation Caption”}, {“Description”, “Example Description”}}),
        #”Filter out non-functions” = Table.SelectRows(#”Renamed Columns”, each ([#”Is Function?”] = true)),
        #”Removed Columns” = Table.RemoveColumns(#”Filter out non-functions”,{“Value”, “ValueType”, “Documentation.Examples”, “Is Function?”}),
        #”Added Alt Category” = Table.AddColumn(#”Removed Columns”, “Alt Category”, each Text.BeforeDelimiter([Function name], “.”), type text),
        #”Added Subcategory” = Table.AddColumn(#”Added Alt Category”, “Subcategory”, each Text.AfterDelimiter([Function name], “.”), type text),
        #”Replaced Value” = Table.ReplaceValue(#”Added Subcategory”,””,””,Replacer.ReplaceText,{“Long Description”}),
        #”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,””,””,Replacer.ReplaceText,{“Long Description”}),
        #”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,””,””,Replacer.ReplaceText,{“Long Description”}),
        #”Replaced Value3″ = Table.ReplaceValue(#”Replaced Value2″,””,””,Replacer.ReplaceText,{“Long Description”}),
        #”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,””,””,Replacer.ReplaceText,{“Long Description”}),
        #”Replaced Value5″ = Table.ReplaceValue(#”Replaced Value4″,””,””,Replacer.ReplaceText,{“Long Description”})

        in
        #”Replaced Value5″

        Copy the information between beginning with let and ending with Value5″. You should exclude the ” at the beginning and the end. I pasted the code between the codes so that it would not change the quotes to HTML quotes

  2. Actually, the =#shared is the only thing working for me. Everything else errors out. I copied and pasted. Then I replaced the quotation marks that were reversed. Then it wouldn’t move past the third line and flagged #”Added Type Column” as an error.
    Then I tried to add each line in individually but it broke at expanding Table.ExpandRecordColumn.
    I am giving up for now but this one just didn’t work for me.

    1. I had an issue with the quote marks, you need to replace all the formatted double quotes with unformatted double-quotes (took me a bit even with find/replace, there were a few different kinds), everything worked after that.

  3. Hi Mat … great work on this project with many tips & tricks to learn. I see the need of removing HTLM code and you are using Power Query (Replace Value). Sometime ago I found an interesting and small code to create a function in Excel for that conversion. I did just some quick tests and it worked well. Thinking if we could create something similar (function) to remove HTLM in PQ

    Function HtmlToText(sHTML) As String
    Dim oDoc As HTMLDocument
    Set oDoc = New HTMLDocument
    oDoc.body.innerHTML = sHTML
    HtmlToText = oDoc.body.innerText
    End Function

    Need to ensure “Microsoft HTML Object LIbrary” is in your References (on VBA window Tools/References)

  4. There is something wrong with the code, because when I copied and pasted I got all kinds of errors. I typed each line exactly as written and while the query “ran” it certainly didn’t give the results I expected.

  5. Thank you for your great trilogy. Hopefully, you’ll emulate the inflationary tendencies of the “Indiana Jones” franchise and extend yours for at least 5 episodes (without us having to wait until 2020 for the 5th one).

  6. Has anybody ever found a way to get the original file name of the analysis services model you are connecting to? If you have multiple pbi desktop files open, DAX Studio seems to be able to list them. I have never been able to do this within Power Query. This would allow for automatic update of the connection parameters. You can already do this by getting the port number from the %localappdata%-folder, but if you have multiple pbi open, you will have multiple Workspace-folders, and there doesn’t seem to be a way to discover which is which.

    1. Dan,
      I just downloaded the file and copied the table out of the file with the “Copy Table” function. Of course I still can’t get the lookup table to work but hey, I have a copy of that one if you want it.

  7. I can’t get this code to work. I tried replacing the multiple types of quotes with standard “”. I continue to get Token Eof expected errors. Hopefully they will fix and repost as I wanted to complete this function project.

  8. These are both DAX and Excel functions:
    FIND
    LEFT
    LEN
    MID
    REPLACE
    RIGHT
    SEARCH

    Unfortunately, you compare
    “LEFT” with “LEFT, LEFTBs”
    etc.

    “Functions in Both” should be 133 instead of 126

    Apart from that, it’s a really nice project!

  9. Hi everyone, The html code has been updated. Thank you all for your patience! You should be able to copy and paste the M code now with no issues.

  10. Hello Everyone, First, thanks Mr. Herring for your work on this and for sharing it. Excellent stuff! I was personally able to get the code in the original article to work by: (1) Copying it to Notepad++ and then doing a mass search and replace on the double quotes found in the code and replacing them with straight quotes (i.e. “); (2) When I copied the code back to the M window there were still errors. (3) I cycled through each error line in the M window and manually replaced any quotes in those lines that did not look like straight quotes. This took about five minutes and finally the code then had no syntax errors and worked YAY! In summary, I don’t know why Notepad++ didn’t handle all the quotes via my step (1) above and I did not bother to investigate why as the mission was accomplished just to get the code working. Hoping this might help someone else! Cheers, Russ

  11. Hi Mat,

    Thanks for your great post but how can I find excel function group table as I don’t know how to find it.
    I get error function group table when I download Pbix file.

    DataSource.Error: Could not find a part of the path ‘C:\Users\MatHerring\Dropbox (PowerPivotPro)\Blog\Posts\BLOG – Matt Herring\Great Function Project\Part 2 – Excel\Function Groups.xlsx’.
    Details:
    C:\Users\MatHerring\Dropbox (PowerPivotPro)\Blog\Posts\BLOG – Matt Herring\Great Function Project\Part 2 – Excel\Function Groups.xlsx

    Best regards,
    Heng

Leave a Comment or Question