How to Import Data in Power BI Using R (2023-01-23)

SQLServerCentral Newsletter for January 23, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Import Data in Power BI Using R]( - [A Version Control Strategy for Database DevOps]( - [SQL Server Function Examples with JSON Files]( - [From the SQL Server Central Blogs - SQL Server Quickie #44 – SQL Server on Docker]( - [From the SQL Server Central Blogs - Updating Documents in Cosmos DB: A Step-by-Step Guide on Creating and Executing a Stored Procedure]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Case expression with NULL]( The Voice of the DBA  Daily Coping Tip Eat healthy today with some nourishing food I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Challenge of Deleting Data We collect a lot of data in our databases. Not as much in bytes as a lot of the video/audio/TikTok/Instagram sites, but still enough that many of us are constantly adding storage to our systems. All this data is not only a challenge to manage, but it also means that we are regularly dealing with query tuning issues. Better code, indexes, and more become regular challenges with large volumes of data. I am a big fan of trying to reduce the data you manage where possible. Archive, delete, remove older data, do something. This not only makes your systems easier to manage and improves performance, but it reduces your risk. Any PII data you have that might store is an ongoing risk in the event of a data breach. I don't pretend this is easy to do in any way, but it's a good idea. If you can remove data (or must because of a regulation like the GDPR), how do you ensure that data is deleted? Most of us know how to submit a DELETE statement, but that just removes the data from an online system. What if you restored or recovered this database tomorrow, would you remember to delete the data again? What about losing a copy of the data or log backup? What about older dev/test systems that were refreshed from production? The data might be in there. If you work through the possible problems, deleting data from a system isn't as simple as you might expect. This might be even more complex in the age of cloud computing, where we don't control the hardware for primary systems, or for backups. There is [an article on deleting data in the cloud]( that talks about the government standards that require that you not only delete data, but that you overwrite the physical hardware to ensure it can't be recovered. This still doesn't address backup systems, but it does help to clarify that many of us might start to demand cloud vendors not only de-allocate the disks we use (or the backup storage), but they also overwrite the storage with zeros. Data security and the risks of not taking this seriously is becoming a bigger issue all the time. I don't know that poor security will cause your organization to fail, but there can be significant costs and possibly reduced employment opportunities. While you might not want to be overly paranoid or concerned about every possible issue, it is worth asking questions of vendors, working through likely scenarios, and trying to quantify risk. More and more systems are regularly under attack from malicious groups, which means we want to minimize simple mistakes, reduce human error, and limit the exposure we have from the data we have by storing only the data we need. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to Import Data in Power BI Using R]( Daniel Calbimonte from SQLServerCentral This article shows how you can use an R script to import data into Power BI. [External Article]( [A Version Control Strategy for Database DevOps]( Additional Articles from Redgate This article explains step one: migrating our monolithic, centralized repos to Git, then implementing a Release Flow branching strategy for parallel development work streams, and a Pull Request workflow to control an automated build and release process. [Technical Article]( [SQL Server Function Examples with JSON Files]( Additional Articles from SQLServerCentral This tip drills down on how to process JSON files with SQL Server and demonstrates an approach for extracting key values from JSON nested key-value pairs. [Blog Post]( From the SQL Server Central Blogs - [SQL Server Quickie #44 – SQL Server on Docker]( Klaus Aschenbrenner from Klaus Aschenbrenner Today I have uploaded SQL Server Quickie #44 to YouTube. This time I’m talking about SQL Server on Docker. From the SQL Server Central Blogs - [Updating Documents in Cosmos DB: A Step-by-Step Guide on Creating and Executing a Stored Procedure]( Basit Farooq from Basit's SQL Server Tips Cosmos DB is a powerful, fully-managed, globally distributed, and multi-model database service provided by Microsoft Azure. One of its key features is the ability to create and execute stored... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Steve Jones - SSC Editor from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Collins):  Case expression with NULL You run this SQL Server code select case when 1=0 then null end; What does the query return? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) More ANSI_PADDING Tables I run this code to create a table and insert rows. What are the results that I see from the query? SET ANSI_PADDING OFF GO CREATE TABLE dbo.ANSIPADDINGOFF(charcol CHAR(20), varcharcol VARCHAR(20)) GO INSERT dbo.ANSIPADDINGOFF (charcol, varcharcol) VALUES ('c test2','v test2 ') GO SELECT '[' + a.charcol + ']', '[' + a.varcharcol + ']' FROM dbo.ANSIPADDINGOFF AS a GO Answer: [c test] and [v test] Explanation: When ANSI_PADDING is off, and the columns are nullable, both fixed and variable character strings do not have trailing blanks. Ref: SET ANSI_PADDING - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Troubles with in memory OLTP performance degradation]( - I started with a new company recently and they use in memory OLTP which is something I haven't used before so I'm excited to try a new technology.  I've been there about a month now and I'm reading/watching everything I can on the feature but they're seeing some strange behavior that I could use some […] SQL Server 2016 - Administration [Job History says running, but Job Activity says idle]( - Hello experts, I'm hoping to get help with a strange issue. I got a request to stop a long-running SQL Agent job. The client correctly sees that the job history says the job is running. But when I checked the Job Activity Monitor, it says the job is idle and last succeeded at such-and-such a […] SQL 2012 - General [60 Second Rolling Avg With Different Starting Points]( - Hi, I have a table with columns: jobId, timeStamp (for duration per jobId) and tempData (see attachment). I have created a query that calculates 60 second rolling average by jobId. (you can see it at the end of the post) Where I am struggling is adding two additional rolling averages. Both of them would be […] SQL Server 2019 - Administration [SQL Server (Express) installation issue]( - Please be gentle.. newbie here ! I'm creating an install program (Using Advanced Installer, but I don't think that's relevant) to install SQL Express. I'm trying to do this as a quiet install with no user intervention. It's failing with a request for a password on the SQL Server Browser. I cant see how to […] [SQL Server 2019 or 2022]( - Hi Experts, We are planning to upgrade our SQL Servers from 2014 to higher version.Which is the best suitable upgrade 2019 or 2022, please advise.   Regards SQL Server 2019 - Development [Existing scalar function within a trigger is not found]( - Calling an existing scalar function within a trigger which is not found within the trigger (Msg 4121, Level 16, State 1) The error is the following: Msg 4121, Level 16, State 1, Procedure Sales.uTu_Customer, Line 12 [Batch Start Line 44] Cannot find either column "Hashing" or the user-defined function or aggregate "Hashing.CreateJsonInputForSha256SalesCustomer", or the name […] [Column name with text and column name]( - Hello all. I hope everyone's ok I have this query: SELECT cont, type, month01, month02, month03... FROM acum Where year=2022 I'd like that for each month it shows a text and the year, like Jan-2022, Feb-2022 and so on. Something like SELECT cont, type, month01 AS CONCAT( 'Jan-',year) , month02 AS CONCAT( 'Feb-',year) ... FROM […] [Stored procedure for top 1 from multiple tables and update 1 table in 1 query]( - I have two tables in this format below; Table Transfer Id Stage Amount TransactionNumber bnumber Table User Id Bnumber FirstName FirstLastName My aim is to select top 1 from these tables based on criteria and then update that record in the Transfer table, all in one query. I have struggled and came up with the […] General Cloud Computing Questions [Suggest cloud provider for SQL]( - I am a MERN stack developer and we need to work with SQL for a project. Can anyone suggest a good FREE cloud SQL server having low storage with which my Node backend can connect using an URI (Like Atlas for MongoDB). Our backend developer is left in the dark with this sudden and urgent […] Azure Data Factory [Data factory question]( - Upon my adventures of Azure data factory and all the wonders it can do I have stumbled upon a issue that Microsoft support has no answer as of yet. Long story short, I am trying to ingest data from a storage blob in another tenant. That customer will only allow us to connect to that […] Reporting Services [Behaviour of a link in a report in ReportViewer]( - Hi. I have an issue with a report that is about 10 years old. The report is running on an ASP.net application that is running off IIS and SQL Server 2017 on a Windows Server 2018 Server machine. The Asp.net application has a Report viewer control to view reports. There are links in the report […] SSRS 2014 [Out of memory (error code)]( - hi I have a problem. I have created a report which shows the data from table in SQL Server (just a "select * from where catid in (..)). There is a parameter where I can choose if it should show MAIN categories, OTHER categories or ALL categories - and depend on choose - the list […] [Out of memory (error code)]( - hi I have a problem. I have created a report which shows the data from table in SQL Server (just a "select * from where catid in (..)). There is a parameter where I can choose if it should show MAIN categories, OTHER categories or ALL categories - and depend on choose - the list […] SQL Server 2005 Integration Services [SSIS Connection Manager OLEDB Issue]( - Hi Team, this is first time I am posting question on forum, I am able to connect to DB using SSMS and windows authentication, however when I am trying to connect in SSIS for new OLEDB connection it seems to give me error as below. Test Connection failed because of an error in initializing provider. […] SQL Server 2022 - Development [Distance between multiple locations]( - What would be the best approach when establishing the distance between points. By that I mean I have lets say 2 fixed Building locations and 8 moving Cranes(Container lifting Crane). I have position locations every minute of the cranes, for all 8 of them. I can establish the distance with the Haversine formula in an […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -