cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nightknight
Frequent Visitor

Invoking Custom Function to insert new rows

Hi,

 

I'd like to build a report that allows users to manually enter values that will be appended into one table. The goal is for the user to enter specific values into parameters, and then this newly entered parameters will insert a new row in the target table.

 

I have built a simple report that allows to add values to 2 parameters, and these 2 parameters are added as a new row in my target table. The problem is that whenever I invoke the function, it creates a new Query. Or whenever I change the values of the 2 parameters, it changes the newly inserted values without adding a new row. I'd like to be able to add a new row in an existing table whenever the user types in new values in the parameters.

 

I currently have the following elements:

  1.  2 parameters
    • parameters.png

 

=> These 2 parameters populate my Table (2):

table populated.png

 

I tried to convert one newly created query (Invoked Function) to a Function:

  •  function enter values with code.png

=> but when I invoke this function, it creates a new query:

  • exmple function used.pngnewly created query.png

 

Function "enter the new values"

 

let
    Source = (Value1 as any, Value2 as any) => let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjJMNlTSAVFGSrE6QL4RhG8E4scCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
        countrows = Table.RowCount(#"Changed Type"),
        inserting = Table.InsertRows(#"Changed Type",countrows,{[Col1=Value1,Col2=Value2]})
    in
        inserting
in
    Source

 

 

The goal would be for the user to add new values into the parameters and these new values are added as a new row to a target table. For example, after having added "new value 1" and "new value2", the user adds "new value3" and "new value4" and the target table looks like this:

goal.png

 

Thanks in advance for your help!

 

5 REPLIES 5
Super User IV
Super User IV

Re: Invoking Custom Function to insert new rows

Hi @nightknight 

 

Power Query does not support inserting data if you want to add records and store them somewhere I would recommend researching Power Apps + SharePoint list combination

 

https://www.youtube.com/watch?v=b9gBXUdd55M

https://powerapps.microsoft.com/en-us/blog/creating-updateable-power-bi-reports-with-powerapps-by-ik...

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


   

Super User IV
Super User IV

Re: Invoking Custom Function to insert new rows

Maybe @ImkeF has an idea.

 

My idea would be to use PowerApps to add parameters to a table. There is a visual for Power BI for PowerApps.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

nightknight
Frequent Visitor

Re: Invoking Custom Function to insert new rows

Hi @Mariusz and @Greg_Deckler ,

 

Many thanks for your answer. I'll give a try with PowerApps.

 

But if someone has a solution that runs in Power BI Desktop only, that would be great.

 

I'll answer here if I find a solution using PowerApps and Sharepoint List.

 

Thanks again!

Super User IV
Super User IV

Re: Invoking Custom Function to insert new rows

You wouldn't have to get SharePoint involved. PowerApps can use just about anything as a data source. And if Power BI was connected via Direct Query to that data source it would be effectively real time. I also think that there is a way to implement PowerAutomate (Flow) to add records to a Power BI dataset, which would effectively make it real time as well. Lots of options. 

 

The problem with Power BI Desktop is that it was written for reading information and displaying it, not for data entry.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-alq-msft
Microsoft

Re: Invoking Custom Function to insert new rows

Hi, @nightknight 

 

Based on my research. If you only want to add one row, it is OK. If you want to add multiple rows, multiple queries will be created. As soon as you invoke the function, a new query is created.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors