MEAL officer learning log

Slicers in Power BI are a type of visual filter that allow users to interactively slice and dice data in a report or dashboard. Slicers provide a way to quickly filter data and focus on specific subsets of information, making it easier to analyze data and gain insights. To create a slicer for filtering dataset by several attributes you can follow next steps: For each column in our future filter, create own separate measure, like: Count BNF = COUNTA (' base '[ Beneficiaries ]) Create a one column table with all options you need to be able to filter. Each record will be show like an option in slicer visual. Create common view measure with SWITCH operator, like: DisplayMeasure = SWITCH ( SELECTEDVALUE ( Distributed [ Distributerd Item ]), " CASH for rent ", [ Count CFR ], " CASH for winterization ", [ Count CFW ], " NFI Kits ", [ Count NFI ], " WASH Kits ", [ Count WASH ], " Beneficiaries ", [ Count BNF ]) Place the new measur...

Step-by-step instruction Sign up at https://www.mapbox.com Go to account webpage and copy default public token or create now token. Add MapBox custom visual into the Power BI document page. Go to Build visual tab and place table columns into Location and Color fields. Go to Format visual -> Visual -> Viz Settings and paste the token into the Access Token field. Disable the Circle checkbox and enable the Choropleth checkbox . Go to MapBox Studio tilesets collection and copy needed map's Tileset ID (use ellipsis button). Open the map and copy the layer name. Copy the field name you need. Go to Format visual -> Visual -> Choropleth and change Data level value to Custom Tileset Paste the information from steps 6-8 into Vector Tile Url , Source Layer Name and Vector Property fields respectively. Done. Notice You have to upload your own maps after registration at MapBox. Map size can be decreased by coordinates values processing with accuracy decreasing (coordinate ...

Today's solved tasks: Made next iteration of particular data cleaning; Prepare aggregated data table for items distribution; Split collected data group by communities. Time used: 4h

Research of the contents of the Middle Name field has been performed. Processed over 5k records; 68% records fixed; Unique middle names amount decreased from 425 to 306; Created preliminary names and middle names reference list; Time used: 4h

Research of the contents of the Settlement field has been performed. Processed over 5k records; 75% records fixed; Unique names amount decreased from 368 to 282; 86 misnaming cases fixed; Completed bringing settlement names to a general appearance; Input typos fixed. Research of the contents of the Name field has been performed. 29 possible name collisions found; 3 records pass; 24 records fixed; 2 irreparable name collisions found. Time used: 3h

First step in geo-based data visualisation design. The Power BI GIS widget has been chosen; Found and checked geo-json files geoBoundaries-UKR-ADM3/ADM2 for districts and communities location; Analysed geo-json files content and geopoints accuracy; Investigated offline json edit tools; [planned] decreasing geopoints accuracy to reduce file size;  Checked district and community naming compatibility between DB and geo-json; Found and analysed naming collisions; [planned] Make changes to the Power BI database structure.  Time used: 5h

In this comprehensive video, we explore the modern data ecosystem and the role of data analytics. Starting from the data analyst role, we delve into the language for data professionals and understanding data repositories and big data platforms. We then move on to the process of gathering, wrangling, analyzing, and mining data, and communicating data analysis findings. The video also covers Excel basics for data analysis, including an introduction to spreadsheets for data analysis, getting started using spreadsheets, basics of data quality and privacy, cleaning data, and data analysis basics such as filtering and sorting data. We also explore using pivot tables. The section on data visualization and dashboards covers creating charts, advanced charts, and dashboards using spreadsheets, as well as using IBM Cognos Analytics for creating dashboards. Moving on to Python for data science, AI, and development, we cover types, expressions and variables, string operations, lists and tuples, d...

The dataset contains the following administrative boundaries: Country - ADM0; States - ADM1; Districts - ADM2; Communities - ADM3. https://data.humdata.org/dataset/geoboundaries-admin-boundaries-for-ukraine   Additional tilesets from Дія: https://data.gov.ua/dataset?&res_format=GeoJSON  

From today, I get started to provide consulting services for [company name] I am planning to write short daily reports about solving tasks include unpersonalised aggregated information for activity efficiency evaluating.

What is sent to Bing Maps? The Power BI service and Power BI Desktop send Bing the geo data it needs to create the map visualization. This may include the data in the Location, Latitude, and Longitude buckets of the visual's field well. Exactly what is sent varies by map type. For maps (bubble, scatter, and dot plot maps), if latitude and longitude are provided, then no data is sent to Bing. Otherwise, any data in the Location bucket is sent to Bing. Filled maps require a field in the Location bucket; even if latitude and longitude are provided. Whatever data is in the Location, Latitude, or Longitude bucket is sent to Bing. In the example below, the field PostalCode is being used for geo-coding, so the values in the PostalCode column are sent to Bing. Data from the Size bucket is not sent to Bing. In this second example below, the field Territory is being used for geo-coding, so the values in the Territory column are sent to Bing. Data from the Legend bucket (and the Col...

ArcGIS overview ArcGIS for Power BI allows users to combine their own data with location-based data from ArcGIS to create interactive maps and dashboards for their business needs. Users can create custom maps, add data points and layers, and perform spatial analysis on their data to gain insights into location-based trends and patterns. They can also overlay demographic data and use other advanced mapping tools to create visually compelling and informative data visualizations. Pros: Options for drivetime and distance radii; Clustering feature as you zoom in or out; Heatmap feature; Option for reference layers from ArcGIS Online; Built-in infographics feature that updates as you move around the map. Cons: Can’t add custom shapes unless added to ArcGIS Online first and shared publicly; Not shown when publishing to web or embedding; Not available for Power BI Report Server. ArcGIS an example Edit mode provides lots of options for map customisation: Basemap (similar to basic map...

Power BI integrates with Bing Maps to provide default map coordinates (a process called geo-coding) so you can create maps. Together they use algorithms to identify the correct location, but sometimes it's a best guess. If Power BI tries, but can't create the map visualization on its own, it enlists the help of Bing Maps. Read more in Bing Maps Documentation .  There are 4 types of core or built-in map visuals: Map (Basic) Filled Map ArcGIS Maps Shape Map Power BI Basic Map Power BI Basic Map is a built-in visualization tool in Power BI that allows users to create basic maps to visualize location-based data. With the Basic Map, users can create a visual representation of their data by plotting points on a map. Users can use the Basic Map to visualize geographic data such as sales by region, customer distribution, store locations, and more. To create a Basic Map in Power BI, users simply need to add location-based data to their report and then select the Basic Map visualization...

To show underlying data for a chart visualization in Power BI, follow these steps: Select the chart visual that you want to view the underlying data for. Click on the "ellipses" (...) at the top right corner of the visual to open the dropdown menu. From the dropdown menu, select "Show as a table" option. Power BI will then display a table with the underlying data for the chart visual. You can scroll through the table to view all the data. If you want to export the underlying data to a CSV or Excel file, click on the "Export data" button at the top right corner of the table. If  you need to show background data related to some point at the chart, use "Show data point as a table" from chart figure context menu. The underlying data depends on selected Legend data field columns and source data table cannot be created by selecting several records from a chart.

The table is created relative to MS SQL Server string functions set. Function Name Description ASCII() Returns the ASCII code value of the leftmost character of a string. SELECT ASCII('A') AS AsciiValue; -- 65 CHAR() Returns a character based on its ASCII code value. SELECT CHAR(65) AS Character; -- A CONCAT() Concatenates two or more strings together. SELECT CONCAT('John', ' ', 'Doe') AS FullName; -- John Doe CONCAT_WS() Concatenates multiple strings together with a specified separator. SELECT CONCAT_WS(', ', 'John', 'Doe', '123 Main St.') AS Address; -- John, Doe, 123 Main St. DIFFERENCE() Compares two strings and returns an integer that represents the difference between them. SELECT DIFFERENCE('apple', 'apples') AS Difference; -- 2 FORMAT() Formats a value with a specified format mask. SELECT FORMA...

The table is created relative to MS SQL Server arithmetical functions set. Function Name Description ABS() Returns the absolute (positive) value of a number. SELECT ABS(-10) -- 10 ACOS() Returns the arc cosine of a number. SELECT ACOS(0.5) -- 1.0471975511966 ASIN() Returns the arc sine of a number. SELECT ASIN(0.5) -- 0.523598775598299 ATAN() Returns the arc tangent of a number. SELECT ATAN(0.5) -- 0.463647609000806 ATN2() Returns the arc tangent of two numbers. SELECT ATN2(1,1) -- 0.785398163397448 CEILING() Returns the smallest integer greater than or equal to a given number. SELECT CEILING(3.14) -- 4 COS() Returns the cosine of a number. SELECT COS(0) -- 1 COT() Returns the cotangent of a number. SELECT COT(1) -- 0.642092615934331 DEGREES() Converts radians to degrees. SELECT DEGREES(1) -- 57.2957795130823 ...

The cource consist of 6 modules: Get started building with Power BI (40 min) Get data with Power BI Desktop (1 hr 15 min) Model data in Power BI (1 hr 7 min) Use visuals in Power BI (1 hr 16 min) Explore data in Power BI (53 min) Publish and share in Power BI (1 hr 4 min) Link for course

To create a Donut Chart, Line Chart, and Pie Chart in Power BI, follow the steps below: Open Power BI Desktop and connect to your data source. Click on the "Visualizations" icon on the right-hand side of the screen. Select the type of chart you want to create from the options: Donut Chart, Line Chart, or Pie Chart. Drag and drop the relevant data fields from your data source onto the chart area. Customize the chart as needed by adjusting the chart settings, such as colors, labels, and formatting. Add any necessary filters, slicers, or other interactive features to the report page to enhance the user experience.

A cursor in SQL Server is a database object that allows us to retrieve each row at a time and manipulate its data. A cursor is nothing more than a pointer to a row. It's always used in conjunction with a SELECT statement. It is usually a collection of SQL logic that loops through a predetermined number of rows one by one. A simple illustration of the cursor is when we have an extensive database of worker's records and want to calculate each worker's salary after deducting taxes and leaves. The SQL Server cursor's purpose is to update the data row by row, change it, or perform calculations that are not possible when we retrieve all records at once. It's also useful for performing administrative tasks like SQL Server database backups in sequential order. Cursors are mainly used in the development, DBA, and ETL processes. The basic structure of a cursor is as follows: Declare the cursor Open the cursor and execute the SELECT statement Fetch the first row While there a...

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc. There are a few rules that subqueries must follow: Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator. The SELECT list cannot include any references to values th...

Database Isolation Levels In MS SQL Server, there are four standard levels of isolation that correspond to the ANSI SQL standard: Read uncommitted : This level of isolation allows transactions to read data that has been modified but not yet committed by other transactions. This can lead to inconsistent results, as a transaction may read data that is subsequently rolled back by another transaction. Read committed : This level of isolation ensures that transactions can read only committed data. A transaction cannot read data that has been modified but not yet committed by another transaction. This provides a higher level of consistency than read uncommitted, but still allows for non-repeatable reads. Repeatable read : This level of isolation ensures that a transaction can read the same data multiple times and get the same result each time, even if other transactions modify the data in the meantime. This prevents non-repeatable reads but can still result in phantom reads (where a transa...

A temporary table in MS SQL Server is a special type of table that is used to store data temporarily. Temporary tables are created in the tempdb database and are available only for the duration of the session or transaction in which they were created. There are two types of temporary tables in MS SQL Server: Local temporary tables: These tables are prefixed with a single pound (#) symbol and are visible only to the session that created them. Local temporary tables are automatically dropped when the session that created them ends. Global temporary tables: These tables are prefixed with a double pound (##) symbol and are visible to all sessions. Global temporary tables are dropped automatically when the last session using them ends. Temporary tables are useful in situations where you need to store intermediate results that are used in multiple steps of a query or stored procedure. For example, if you need to perform a series of calculations on a large data set, you could store the in...

Name Description ADDDATE() Adds dates: SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); -- 1998-02-02 ADDTIME() Adds time: SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002'); -- 1998-01-02 01:01:01.000001 CONVERT_TZ() Converts from one timezone to another: SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); -- 2004-01-01 22:00:00 CURDATE(), CURRENT_DATE(), CURRENT_DATE Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context: SELECT CURDATE(); -- 1997-12-15 SELECT CURDATE() + 0; -- 19971215 CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW() CURTIME(), CURRENT_TIME(), CURRENT_TIME Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a str...

A SQL transaction is a set of SQL statements that are executed as a single unit of work, which means that either all the statements are executed successfully or none of them are. Transactions are used in SQL to ensure the integrity of the database and to guarantee that data is consistent even in the presence of system failures or concurrent access by multiple users. Here are some key concepts related to SQL transactions: Atomicity : The atomicity property of transactions ensures that all the SQL statements in a transaction are treated as a single unit of work, which means that either all the statements are executed successfully or none of them are. If any statement in the transaction fails, the entire transaction is rolled back to its original state. Consistency : The consistency property of transactions ensures that the database remains in a consistent state before and after the transaction is executed. If a transaction violates any constraints, such as primary key or foreign key co...

SQL views are virtual tables that are based on the result set of a SQL SELECT statement. A view is essentially a saved query that can be used like a table in SQL queries, but it doesn't actually store any data itself. Instead, it retrieves its data from one or more tables or other views. Here are some of the key features and benefits of SQL views: Simplify complex queries : Views can simplify complex queries by breaking them down into smaller, more manageable parts. By creating a view that retrieves the data needed for a particular task, you can simplify the queries that use that view. Provide a layer of abstraction : Views can provide a layer of abstraction between the physical tables and the users or applications that access them. By presenting a simplified view of the data, views can make it easier for users and applications to work with the database. Control data access : Views can be used to control access to sensitive data by limiting the columns and rows that are exposed to...

SQL indexes are database objects that are used to improve the performance of database queries. They provide a way to quickly locate data in a table by creating a reference to the data based on one or more columns. When a SQL query is executed, the database engine uses the index to quickly locate the rows that match the criteria specified in the query. This is done by searching the index rather than scanning the entire table, which can be a time-consuming process, especially for large tables. Indexes can be created on one or more columns of a table and can be of different types, such as clustered, non-clustered, unique, and composite. The choice of index type depends on the specific needs of the application and the characteristics of the data. Creating indexes on frequently queried columns can significantly improve the performance of SQL queries, especially for large datasets. However, it's important to keep in mind that indexes also have some overhead, such as increased storage an...

<?xml version="1.0" encoding="utf-8" ?> <!-- SQL XML created by WWW SQL Designer, https://github.com/ondras/wwwsqldesigner/ --> <!-- Active URL: https://sql.toad.cz/?keyword=default --> <sql> <datatypes db="mssql"> <group label="Integer" color="rgb(238,238,170)"> <type label="TinyInt" length="0" sql="tinyint" re="INT" quote="" bytes="1" note="Integer data: 0 to 255"/> <type label="SmallInt" length="0" sql="smallint" re="INT" quote="" bytes="2" note="Integer data: -32,768 to 32,767"/> <type label="Int" length="0" sql="int" re="INT" quote="" bytes="4" note="Integer data: -2,147,483,648 to 2,147,483,647"/> <type label="BigInt" length="0" sql=...