Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Suggestion for pasting "Entered Data" M Code

Greetings,

 

Many of our solutions involve copying and pasting the user's data into Power Query via "Enter Data", or we make example tables using the same method.  Many of you then use the M code from the "Enter Data" table in the solution that you present.  Although this is convenient, consider the M code generated for a simple table like:

 

Column1Column2

a 1
b 2
c 3

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozArGQgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}})
in
#"Changed Type"

 

If you are going to use "Enter Data" to present your solution, refer to your "Enter Data" table as the source, and use this reference query as your solution code.  Why present your solution as above, when you could present it as:

 

let
Source = #"Enter Data Table",

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}})

in
#"Changed Type"

 

Let's start doing this right away, please.  No more pasting the "Enter Data" code, just refer to that table as the source in your M code solutions.

 

Thanks!

 

--Nate

General Comment

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Keep in mind that this runs afoul of multiple design principles.  Among others  "No redundant steps"  and "keep everything in a singe partition" 

     

    Instead, use the #table syntax.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Are you of the opinion that a reference query is a redundant step? And what does it mean to "keep everything in the same partition"?

     

    At any rate, my goal is to provide these folks with solutions that are the most simple and straightforward to understand, and to implement. What's easier to understand:

     

    let

    Source = YourProblemTable,

     

    or

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozArGQgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),

     

    --Nate

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous, the reason why I use "Enter Data" a lot is because my code can also help someone in future. Imagine some newbie trying to find solution of their problem. They find my code and after pasting to blank query they can immediately see the solution. Without "Enter Data" step he wouldn't be able to see what is every single step doing because they are missing proper Source step. For "Enter Data" purpose I'm using explanation how to use my query in my signature below (there is one spelling mistake, but I'm sure they understand how to use it).

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      True, but that newbie still has to then integrate his own dataset into his solution. No data analyst is going to leave the "Enter data" table in their actual business document, which will lead to another question--"How do I use my table in the solution?" The "Enter data" M code is not code that any human would actually write.

       

      --Nate

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        would this help?

         

        Table.FromRecords({
        [Name = "CustomerID", Value = 1],
        [Name = "Name", Value = "Bob"],
        [Name = "Phone", Value = "123-4567"]
    })