how to extract data in power query
- User
how to extract data in power query
Assistant
Power Query! A fantastic tool for data extraction, transformation, and loading (ETL) in Microsoft Excel and Power BI. Here's a comprehensive guide on how to extract data using Power Query:
What is Power Query? Power Query is a data connection technology that allows you to discover, connect, combine, and refine data from various sources. It's a powerful tool for data extraction, transformation, and loading.
Getting Started with Power Query
- Excel: Go to
Datatab >Get & Transform Datagroup >New Query(or pressAlt + F + P + N). - Power BI: Go to
Hometab >Edit Queries>New Query(or pressAlt + F + P + N).
This will open the Power Query Editor.
Extracting Data from Various Sources Power Query supports extracting data from numerous sources. Here are some common ones:
- Web Page:
- Click
From Web(Excel) orNew Source>Web(Power BI). - Enter the URL of the webpage containing the data you want to extract.
- Power Query will detect tables on the webpage. Select the table(s) you want to extract.
- Click
- Excel File:
- Click
From File>From Workbook(Excel) orNew Source>Excel(Power BI). - Select the Excel file (.xlsx, .xls, .xlsm, etc.) containing the data.
- Choose the worksheet or table you want to extract.
- Click
- CSV/Text File:
- Click
From File>From Text/CSV(Excel) orNew Source>Text/CSV(Power BI). - Select the CSV or text file (.csv, .txt, etc.) containing the data.
- Click
- Database (e.g., SQL Server, Oracle, MySQL):
- Click
From Database> select the database type (Excel) orNew Source>Database> select the database type (Power BI). - Enter the server name, database name, username, and password (if required).
- Select the table(s) or write a SQL query to extract data.
- Click
- Azure Blobs, Azure Tables, or other Cloud Sources:
- Click
From Azure(Excel) orNew Source>Azure(Power BI). - Authenticate with your Azure account and select the desired source.
- Click
- Other Sources: OData feeds, SharePoint lists, Active Directory, Hadoop, etc.
Extracting Data using Power Query Editor Once you've connected to your data source, you'll be in the Power Query Editor. Here, you can:
- Preview data: See a sample of the data in the
Previewpane. - Select columns: Check the boxes next to the columns you want to extract.
- Filter rows: Use the filter buttons (e.g.,
=,≠,>,<) to narrow down the data. - Transform data: Use various transformation tools (e.g.,
Group By,Pivot,Unpivot,Split Column) to shape your data. - Remove errors: Click
Remove Errorsto eliminate rows with errors.
Common Power Query Extraction Techniques
- Table Extraction: Extract entire tables from web pages, Excel files, or databases.
- Use
Table Tools>Detect Tables(Web) or select the table from the navigator (Excel files, databases).
- Use
- HTML Table Extraction: Extract specific tables from web pages.
- Use
Web>Advanced>Options>Select Tablesand choose the desired table.
- Use
- XPath Extraction: Extract data from web pages using XPath expressions.
- Use
Web>Advanced>Options>XPathand enter your XPath query.
- Use
- Regex (Regular Expressions) Extraction: Extract patterns from text columns.
- Use
Add Column>Extract>Regexand enter your regex pattern.
- Use
- Json.Document Extraction: Extract data from JSON files or web APIs.
- Use
From Other Sources>JSONand load the JSON file or enter the API URL.
- Use
Loading Extracted Data Once you've transformed and refined your data:
- Load: Click
Load(or pressCtrl + Enter) to load the data into:- Excel: a new worksheet or a PivotTable.
- Power BI: the data model for reporting and visualization.
- Load To: Choose
Load To>Connection Onlyto create a query that can be reused or referenced in other queries. - Close & Load: Close the Power Query Editor and load the data into your workbook or data model.
Tips & Tricks
- Use
Advanced Editor(View>Advanced Editor) to write custom M code for complex data extraction tasks. Duplicatequeries to create variations of your extraction process.Referencequeries to build upon existing queries.- Use
Parametersto make your queries dynamic and reusable. Refreshyour queries to update the extracted data.
Now, go forth and extract data like a pro with Power Query!
Additional Resources
- Microsoft Power Query documentation: https://docs.microsoft.com/en-us/power-query/
- Power Query M Language reference: https://docs.microsoft.com/en-us/powerquery-m/
- Power BI Tutorials: https://docs.microsoft.com/en-us/power-bi/
Happy data extracting!
- Excel: Go to
- User
extract part of a sentence from a column and put in a another column in power query
Assistant
A fantastic Power Query task! Extracting part of a sentence from a column and creating a new column with the extracted text is a common requirement. I'll show you how to do this using various methods in Power Query.
Sample Data Let's assume you have a table
Sentenceswith a columnFull Sentence:Full Sentence The quick brown fox jumps over the lazy dog. Power Query is amazing for data manipulation. Extracting data is fun with Power Query tools. You want to extract a specific part of the sentence, e.g., the word after "Power" or the text between "Extracting" and "with".
Method 1: Using
Text.AfterDelimiter(Extract text after a specific word/phrase)Suppose you want to extract the text after the word "Power" in each sentence.
- Go to
Add Columntab >Text Columngroup >Extract>Text After Delimiter. - In the
Text After Delimiterdialog:- Delimiter: enter
Power(case-sensitive). - Start searching from: choose
Start of string. - End delimiter: leave blank (we want all text after "Power").
- Click
OK.
- Delimiter: enter
A new column
Text After Delimiteris added:Full Sentence Text After Delimiter The quick brown fox jumps over the lazy dog. Power Query is amazing for data manipulation. Query is amazing for data manipulation. Extracting data is fun with Power Query tools. Method 2: Using
Text.BetweenDelimiters(Extract text between two specific words/phrases)Now, let's extract the text between "Extracting" and "with" in each sentence.
- Go to
Add Columntab >Text Columngroup >Extract>Text Between Delimiters. - In the
Text Between Delimitersdialog:- Start delimiter: enter
Extracting(case-sensitive). - End delimiter: enter
with(case-sensitive). - Start searching from: choose
Start of string. - Click
OK.
- Start delimiter: enter
A new column
Text Between Delimitersis added:Full Sentence Text Between Delimiters The quick brown fox jumps over the lazy dog. Power Query is amazing for data manipulation. Extracting data is fun with Power Query tools. data is fun Method 3: Using
Text.PositionOfandText.Range(Extract text at a specific position)Suppose you want to extract the 5th word from each sentence.
- Add a new column:
Add Columntab >Custom Column. - In the
Custom Columnformula box, enter:
= Text.Range([Full Sentence], Text.PositionOf([Full Sentence], " ", 4) + 1, Text.PositionOf([Full Sentence], " ", 5) - Text.PositionOf([Full Sentence], " ", 4) - 1)Here's what's happening:
Text.PositionOf([Full Sentence], " ", 4)finds the position of the 4th space (before the 5th word).Text.PositionOf([Full Sentence], " ", 5)finds the position of the 5th space (after the 5th word).Text.Rangeextracts the text between these positions ( + 1 to exclude the space).
Name this new column
5th Word.Full Sentence 5th Word The quick brown fox jumps over the lazy dog. jumps Power Query is amazing for data manipulation. for Extracting data is fun with Power Query tools. with Method 4: Using
Splitter.SplitTextByDelimiter(Split text into multiple columns)If you want to split the sentence into separate columns using a delimiter (e.g., space), use:
Transformtab >Text Columngroup >Split Column>By Delimiter.- Choose
Spaceas the delimiter and selectSplit into RowsorSplit into Columns.
This will create new columns
Full Sentence.1,Full Sentence.2, ...,Full Sentence.N.Method 5: Using Regular Expressions (Regex) with
Text.CombineandList.AccumulateFor advanced users, you can use Regex to extract patterns from text. Suppose you want to extract all words starting with "P".
- Add a new column:
Add Columntab >Custom Column. - In the
Custom Columnformula box, enter:
= Text.Combine( List.Accumulate( Text.Split([Full Sentence], " "), {}, (state, current) => if Text.StartsWith(current, "P") then state & {current} else state ), ", " )Here's what's happening:
Text.Splitsplits the sentence into words.List.Accumulateiterates through the words, checking if each word starts with "P" usingText.StartsWith.- If true, the word is added to the accumulated list.
Text.Combinejoins the extracted words with commas.
Name this new column
Words Starting with P.Full Sentence Words Starting with P The quick brown fox jumps over the lazy dog. Power Query is amazing for data manipulation. Power, Query Extracting data is fun with Power Query tools. Power, Query These are just a few examples of extracting part of a sentence in Power Query. Choose the method that best fits your needs!
Tips
- Use the
Advanced Editor(View>Advanced Editor) to write custom M code for complex text extraction tasks. - Experiment with different
Textfunctions (e.g.,Text.Contains,Text.StartsWith,Text.EndsWith) to create more sophisticated extraction logic. - Use
Error Handling(e.g.,try-otherwiseblocks) to manage errors when extracting text.
Happy extracting!
Additional Resources
- Microsoft Power Query documentation: https://docs.microsoft.com/en-us/power-query/
- Power Query M Language reference: https://docs.microsoft.com/en-us/powerquery-m/
- Text functions in Power Query: https://docs.microsoft.com/en-us/powerquery-m/text-functions
Let me know if you have any questions or need further assistance!
- Go to
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.