Anahi  Kirlin

Anahi Kirlin

1598502780

Discover the EASY way to Transform Text in Excel (No Formulas - just Power Query)

Discover the EASIEST way to perform text transformations in Excel. Power Query is the perfect tool for you to solve these tasks that can be quite complicated with just Excel formulas.

Usually, solving these transformations in Excel are NOT an easy task. If you want to do any of the following with Excel formulas, things can get scary very fast:

  • Separate words in Excel by a delimiter
  • Split a column in Excel based on a certain logic in multiple columns
  • Separate text from numbers in a cell
  • Extract numbers from the middle of a text string
  • Extract text after a specific character in Excel

But fortunately you have another option: Power Query aka Get and Transform. With this magic tool you can achieve this by just clicking a few buttons. And if this was not already enough there is another upside: Once you’ve setup the logic in Power Query, when new data comes in all you have to do is to click Refresh. And you’re done!

In this tutorial I’ll show you how you can use Power Query to perform some common text transformation tasks. See for yourself how easy these tasks become with some basic Power Query understanding.

Download the Workbook: https://www.xelplus.com/excel-text-transformation-power-query

#excel #developer

What is GEEK

Buddha Community

Discover the EASY way to Transform Text in Excel (No Formulas - just Power Query)
Gerhard  Brink

Gerhard Brink

1622622360

Data Validation in Excel

Data Validation in Excel

In this tutorial, let’s discuss what data validation is and how it can be implemented in MS-Excel. Let’s start!!!

What Is Data Validation in Excel?

Data Validation is one of the features in MS-Excel which helps in maintaining the consistency of the data in the spreadsheet. It controls the type of data that can enter in the data validated cells.

Data Validation in MS Excel

Now, let’s have a look at how data validation works and how to implement it in the worksheet:

To apply data validation for the cells, then follow the steps.

1: Choose to which all cells the validation of data should work.

2: Click on the DATA tab.

3: Go to the Data Validation option.

4: Choose the drop down option in it and click on the Data Validation.

data validation in Excel

Once you click on the data validation menu from the ribbon, a box appears with the list of data validation criteria, Input message and error message.

Let’s first understand, what is an input message and error message?

Once, the user clicks the cell, the input message appears in a small box near the cell.

If the user violates the condition of that particular cell, then the error message pops up in a box in the spreadsheet.

The advantage of both the messages is that the input and as well as the error message guide the user about how to fill the cells. Both the messages are customizable also.

Let us have a look at how to set it up and how it works with a sample

#ms excel tutorials #circle invalid data in excel #clear validation circles in excel #custom data validation in excel #data validation in excel #limitation in data validation in excel #setting up error message in excel #setting up input message in excel #troubleshooting formulas in excel #validate data in excel

Anahi  Kirlin

Anahi Kirlin

1598502780

Discover the EASY way to Transform Text in Excel (No Formulas - just Power Query)

Discover the EASIEST way to perform text transformations in Excel. Power Query is the perfect tool for you to solve these tasks that can be quite complicated with just Excel formulas.

Usually, solving these transformations in Excel are NOT an easy task. If you want to do any of the following with Excel formulas, things can get scary very fast:

  • Separate words in Excel by a delimiter
  • Split a column in Excel based on a certain logic in multiple columns
  • Separate text from numbers in a cell
  • Extract numbers from the middle of a text string
  • Extract text after a specific character in Excel

But fortunately you have another option: Power Query aka Get and Transform. With this magic tool you can achieve this by just clicking a few buttons. And if this was not already enough there is another upside: Once you’ve setup the logic in Power Query, when new data comes in all you have to do is to click Refresh. And you’re done!

In this tutorial I’ll show you how you can use Power Query to perform some common text transformation tasks. See for yourself how easy these tasks become with some basic Power Query understanding.

Download the Workbook: https://www.xelplus.com/excel-text-transformation-power-query

#excel #developer

Hal  Sauer

Hal Sauer

1592656080

Data Visualization in Microsoft Power BI

Introduction
The final article will take us through Power BI Visualizations and how to create them using the data sets created previously in these series. The walkthrough will cover 3 basic Visualizations: Table, Pie Chart, and Line Chart. These will then combine to create a basic report/dashboard within Power BI.

How to create a table
The table visual is one of the more basic options available to us, however, possibly one of the most common also! Like an Excel sheet, we can create a ‘list’ within the visual to which we can scroll and filter.

#microsoft office power bi #programming #excel #excel power query #microsoft office #power bi #sql

Pink  Rosenbaum

Pink Rosenbaum

1594434335

Loading and Managing Data within Microsoft Power BI

Introduction

This article will cover off importing data, manipulating data, and finally managing relationships within Power BI. Again, like the previous article, this one will focus on the basic principles of using Power BI, as opposed to an in-depth walkthrough.

Importing data from Excel, SQL Server, and the Web

Importing data from Excel into Power BI

When starting out, Excel should be the first data source you look to when it comes to importing data. The main reasons for this being: Ease of Use, Transparency, and General Availability.

Step 1 – Excel Data Set

This is the data set we’re going to import into Power BI, it contains footwear pricing from a range of retailers. Ensure your data has some key measures which we can later analyze – the data below includes dates, pricing, and counts.

Step 2 – Open Power BI

Open Power BI from the windows menu, the below box will appear where you can then navigate either to your recent sources or open shared files. To simply import data, click the ‘Get Data’ option.

Step 3 – Select Excel

Once clicked through, a series of options will be displayed in how we can import data into Power BI. To begin, click the ‘Excel’ option highlighted below.

Step 4 – Locate Data Set

Locate the Excel file to upload and click ‘Open’.

Step 5 – Navigator Screen

Once the Excel file has been imported, the Navigator will be displayed (please wait a few seconds depending on Excel file size). Then click into the checkbox located next to the name of the Excel sheet, this will then create a preview in the right-hand side screen.

Step 6 – Load Data

We then have the choice to either ‘Load’ the data or ‘Transform’ the data. Loading the data will import the data straight into the Power BI interface whilst transforming will allow you to begin editing and manipulating your data. For now, lets ‘Load’ the data, and we’ll head back to the ‘Transform’ stage later in the article.

Step 7 – Data Loading

A loading screen will now pop up, again, the loading time will vary, depending on your data set size. Once complete, the loading box will automatically disappear.

Step 8 – Data Loaded.

The Excel sheet has now been successfully loaded into Power BI. The detail sheet has been automatically categorized based on the first line of data in the Excel (Titles). As you can see, some of the information has already been formatted based on existing formats, Text, Number, and Dates.

Step 9 – Quick Data Check

To quickly verify that your data was imported, we can create a quick ‘Table’ by clicking on the icon highlighted below. Followed by clicking into the checkboxes against your data, this will populate the values. For example, I’ve checked ‘Brand’ and ‘Product ID’ – this has then displayed the count of products each brand has within the data set.

#microsoft office power bi #programming #excel #excel power query #microsoft office #power bi #sql

Ahebwe  Oscar

Ahebwe Oscar

1620185280

How model queries work in Django

How model queries work in Django

Welcome to my blog, hey everyone in this article we are going to be working with queries in Django so for any web app that you build your going to want to write a query so you can retrieve information from your database so in this article I’ll be showing you all the different ways that you can write queries and it should cover about 90% of the cases that you’ll have when you’re writing your code the other 10% depend on your specific use case you may have to get more complicated but for the most part what I cover in this article should be able to help you so let’s start with the model that I have I’ve already created it.

**Read More : **How to make Chatbot in Python.

Read More : Django Admin Full Customization step by step

let’s just get into this diagram that I made so in here:

django queries aboutDescribe each parameter in Django querset

we’re making a simple query for the myModel table so we want to pull out all the information in the database so we have this variable which is gonna hold a return value and we have our myModel models so this is simply the myModel model name so whatever you named your model just make sure you specify that and we’re gonna access the objects attribute once we get that object’s attribute we can simply use the all method and this will return all the information in the database so we’re gonna start with all and then we will go into getting single items filtering that data and go to our command prompt.

Here and we’ll actually start making our queries from here to do this let’s just go ahead and run** Python manage.py shell** and I am in my project file so make sure you’re in there when you start and what this does is it gives us an interactive shell to actually start working with our data so this is a lot like the Python shell but because we did manage.py it allows us to do things a Django way and actually query our database now open up the command prompt and let’s go ahead and start making our first queries.

#django #django model queries #django orm #django queries #django query #model django query #model query #query with django