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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
luojiandan_bi
Helper I
Helper I

Power Query table to SQL Server by R script,why is that.0 extra

001.png

In Power Query Editor, the text string is: 123547126811

Why is the text string in SQL Server: 123547126811.0?

R Script: 

 

 

library(RODBC)
conn <- odbcDriverConnect("driver={SQL Server}; server=localhost;Database=**;uid=uid;pwd=password")
odbcClearError(conn)
sql="Delete T From
 (Select Row_Number() Over(Partition By cust_id order By cust_id) As RowNumber,* From tmp)T
 Where T.RowNumber > 1"
sqlSave(conn, dataset, tablename="tmp",rownames=FALSE, safer=FALSE, append=TRUE,fast=TRUE)
sqlQuery(conn,sql)  #delete duplicate data.
close(conn)

 

 

because The PowerQuery M code run twice,so run delete duplicate sql in R Script.

1 ACCEPTED SOLUTION
luojiandan_bi
Helper I
Helper I

using format function is ok:

 

dataset$cust_id <- format(dataset$cust_id)

 

View solution in original post

3 REPLIES 3
luojiandan_bi
Helper I
Helper I

using format function is ok:

 

dataset$cust_id <- format(dataset$cust_id)

 

v-kelly-msft
Community Support
Community Support

Hi @luojiandan_bi ,

 

Check whether the data type has been changed automatically in power query,if so,close it.

Check the reference below:

https://a4accounting.com.au/stop-power-query-from-automatically-setting-data-types/

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

luojiandan_bi
Helper I
Helper I

wait for helping online.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors