Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ericOnline
Post Patron
Post Patron

Is Power Query Lang. being actively developed?

I ask because I'd love to see the language develop a bit (says the "new guy", sheesh). This is based on a simple column reorder, moving the column "localTime" from the right end of a list to the left end results in code like this!:

 

    #"Reordered Columns" = Table.ReorderColumns(
        #"Filtered Rows",
            {
                "WEATHER STATION CODE", 
                "DATA DATE and HOUR", 
                "TIME ZONE", 
                "localTime", 
                "° LATITUDE of STATION", 
                "° LONGITUDE of STATION", 
                "ELEVATION of STATION (Feet)", 
                "TEMPERATURE °F", "HEAT INDEX °F", 
                "WIND CHILL °F", 
                """FEELS-LIKE"" TEMPERATURE °F", 
                "WIND CHILL ENERGY, W/M2", 
                "HIGHEST TEMPERATURE °F", 
                "LOWEST TEMPERATURE °F", 
                "DEW POINT TEMPERATURE °F", 
                "NORMAL DEW POINT TEMPERATURE °F", 
                "RELATIVE HUMIDITY (Percent; %)", 
                "NORMAL RELATIVE HUMIDITY (Percent; %)", 
                "WET BULB TEMPERATURE °F", 
                "NORMAL WET BULB TEMPERATURE °F", 
                "WIND DIRECTION  (Degrees, True)", 
                "WIND SPEED (M.P.H.)", 
                "MAXIMUM INTRA HOUR WIND SPEED (M.P.H.)", 
                "SPEED GUST (if any, M.P.H.) ", 
                "NORMAL WIND SPEED (M.P.H.)", 
                "CLOUD COVER (Percent; %)", 
                "NORMAL CLOUD COVER (Percent; %)", 
                "CLOUD CEILING (x 100-Feet)", 
                "CLOUD COVER (Type)", 
                "COVERAGE, LOW (Percent; %)", 
                "COVERAGE, MIDDLE (Percent; %)", 
                "COVERAGE, HIGH (Percent; %)", 
                "VISIBILITY (Miles)", 
                "MINIMUM INTRA HOUR VISIBILITY (Miles)", 
                "NORMAL VISIBILITY (Miles)", 
                "SOLAR IRRADIANCE (Beam Horizontal, W/M2)", 
                "SOLAR IRRADIANCE (Diffuse, W/M2)", 
                "SOLAR IRRADIANCE (Global Horizontal, W/M2)", 
                "SOLAR IRRADIANCE POSSIBLE (Beam Horizontal, on a Clear Hour, W/M2)", 
                "SOLAR IRRADIANCE POSSIBLE (Global Horizontal, on a Clear Hour, W/M2)", 
                "SOLAR IRRADIANCE (ET, W/M2)", "SOLAR IRRADIANCE, PERCENT of POSSIBLE (Direct)", 
                "SOLAR IRRADIANCE, PERCENT of POSSIBLE (Global)", 
                "MINUTES of SUNSHINE (WBI Calculated)", 
                "NORMAL MINUTES of SUNSHINE (WBI Calculated)", 
                "MINUTES of SUNSHINE (Total Possible)", 
                "SUN ANGLE, °ABOVE HORIZON", 
                "SUN ANGLE, °FROM ZENITH", 
                "SUNSHINE, PERCENT of POSSIBLE (Calculated / Total Possible)", 
                "NORMAL SUNSHINE, PERCENT of POSSIBLE (Calculated / Total Possible)", 
                "SOLAR IRRADIANCE (Beam Normal, W/M2)", 
                "SOLAR IRRADIANCE POSSIBLE (Beam Normal on a Clear Hour, W/M2)", 
                "NORMAL WATER EQUIVALENT (Inches)", 
                "WATER EQUIVALENT AWXCLIMO (Inches)", 
                "NORMAL SNOWFALL, WBI (calculated; Inches)", 
                "SNOWFALL, FINAL (AWXCLIMO, Inches)", 
                "PRESSURE (Inches)", 
                "PRESSURE (Millibars)", 
                "NORMAL PRESSURE (Inches)", 
                "NORMAL PRESSURE (Millibars)", 
                "WBI 2-DIGIT NUMERIC WEATHER CODE", 
                "WBI 2-4 ALPHA NUMERIC WEATHER CODE", 
                "WBI WEATHER TEXT"
            }
        )

Now, I might be doing it wrong, i'm new here. But I feel like the equivalent in Python would be something like:

move column "localTime" where I want it
    please
thanks

Says the guy who knows 0 Python (but who does know clean code when he sees it 🙂 )

 

What are your thoughts on this?

4 REPLIES 4
Greg_Deckler
Super User
Super User

Yes, I do believe that M is being actively developed. From a computer language theorists perspective, comparing Python with M is apples and oranges. Python is an object-oriented, imperative, functional, procedural, reflective language while M is a mostly pure, higher-order, dynamically typed, partially lazy functional language. I mean, come on, like how different can you get??

 

All of that aside, M does tend to be tied to column names too much and can be rather verbose when working with them. @ImkeF do you have any thoughts on this topic?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler. I totally get what you're saying. 

 

From a "data visualization" perspective (output of effort), Python and its plugins are what Power BI is compared with. At least within my org. So when we go to code review and we're comparing lines of code (apples) with actual charts (apples) from both tools... the tools do have instances where they are comparable.

 

Thank you for the insights and expertise!

Some thoughts in random order:

  • The M-language is actively developed by Microsoft, but the community that builds additional packages isn't big (yet, hopefully). This might have something to do with the fact that there is still no elegant solution to use package-like additions on an enterprise level (yet, hopefully...) You can check out my GitHub-Repo to get some ideas what could be added: https://github.com/ImkeF/M
  • The M-language that is autogenerated by the steps you take in the UI can be as clumsy as with the VBA-Assistant in Excel. You can use more elegant code if you know the language.
  • In Python, you can use just the same "clumsy" technique to reorder column like the M-code you've pasted above.
  • I you prefer Python to M, you can use it in Power BI instead of M. There is an option to run Python-scripts in the Query Editor and you can do all you imports and transformation via Python (or R) instead if you feel more comfortable with it.

With regards to your pseudocode: How would you expect the "where I want it" to be determined?:

  1. Move my field x positions to the right (or left) ("Relative number")
  2. Move my field x next to field y ("Relative before or after")
  3. Move my field to the xth position of the table ("Absolute")

Of course it's possible to write an M-function for all of them that will just need the name of the x (and y) -column to be specified.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Nice, thank you for the thoughts, I do appreciate the engagement. The purpose of the OP was to "prod" the community a little to see what came of it.

 

RE:  Your Github library:

  • What a treasure! Hopefully I can submit some PR's and pass the project I've been working on (API POST request - JSON - table)

RE: Elegant M:

  • Yes, the auto-generated M is tough to reproduce consistently. I do need to get to know the language.

RE: Python:

  • I have no preference for language (I don't truly know either one!). It is very cool to know Python is supported by Power BI. I was unaware of that.

RE: Pseudocode:

  • More of a joke on my part. I do feel like M is being quite literal and perhaps even missing a layer of abstraction that would simplify things (certainly in the M example above). 
  • There likely IS a layer of abstraction that can be implemented, I'm just not sure of how to learn/implement it. 

I look forward to continue the path of learning and sharing here in the community. 

 

Take care!

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.