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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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.

Greg_Deckler
Super User
Super User

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.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

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.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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-ike-ellis/

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn


   

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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