Skip to content
TheChatArchiveThe Chat Archive
Metallama-4-maverick-03-26-experimental· MetaHugging Face LMArena Human Preference 140K

how to extract data in power query

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen3-30b-a3b’s answer.
  1. User

    how to extract data in power query

  2. Meta 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

    1. Excel: Go to Data tab > Get & Transform Data group > New Query (or press Alt + F + P + N).
    2. Power BI: Go to Home tab > Edit Queries > New Query (or press Alt + 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:

    1. Web Page:
      • Click From Web (Excel) or New 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.
    2. Excel File:
      • Click From File > From Workbook (Excel) or New Source > Excel (Power BI).
      • Select the Excel file (.xlsx, .xls, .xlsm, etc.) containing the data.
      • Choose the worksheet or table you want to extract.
    3. CSV/Text File:
      • Click From File > From Text/CSV (Excel) or New Source > Text/CSV (Power BI).
      • Select the CSV or text file (.csv, .txt, etc.) containing the data.
    4. Database (e.g., SQL Server, Oracle, MySQL):
      • Click From Database > select the database type (Excel) or New 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.
    5. Azure Blobs, Azure Tables, or other Cloud Sources:
      • Click From Azure (Excel) or New Source > Azure (Power BI).
      • Authenticate with your Azure account and select the desired source.
    6. 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:

    1. Preview data: See a sample of the data in the Preview pane.
    2. Select columns: Check the boxes next to the columns you want to extract.
    3. Filter rows: Use the filter buttons (e.g., = , ≠, >, <) to narrow down the data.
    4. Transform data: Use various transformation tools (e.g., Group By, Pivot, Unpivot, Split Column) to shape your data.
    5. Remove errors: Click Remove Errors to eliminate rows with errors.

    Common Power Query Extraction Techniques

    1. 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).
    2. HTML Table Extraction: Extract specific tables from web pages.
      • Use Web > Advanced > Options > Select Tables and choose the desired table.
    3. XPath Extraction: Extract data from web pages using XPath expressions.
      • Use Web > Advanced > Options > XPath and enter your XPath query.
    4. Regex (Regular Expressions) Extraction: Extract patterns from text columns.
      • Use Add Column > Extract > Regex and enter your regex pattern.
    5. Json.Document Extraction: Extract data from JSON files or web APIs.
      • Use From Other Sources > JSON and load the JSON file or enter the API URL.

    Loading Extracted Data Once you've transformed and refined your data:

    1. Load: Click Load (or press Ctrl + Enter) to load the data into:
      • Excel: a new worksheet or a PivotTable.
      • Power BI: the data model for reporting and visualization.
    2. Load To: Choose Load To > Connection Only to create a query that can be reused or referenced in other queries.
    3. 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.
    • Duplicate queries to create variations of your extraction process.
    • Reference queries to build upon existing queries.
    • Use Parameters to make your queries dynamic and reusable.
    • Refresh your queries to update the extracted data.

    Now, go forth and extract data like a pro with Power Query!

    Additional Resources

    Happy data extracting!

  3. User

    extract part of a sentence from a column and put in a another column in power query

  4. Meta 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 Sentences with a column Full 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.

    1. Go to Add Column tab > Text Column group > Extract > Text After Delimiter.
    2. In the Text After Delimiter dialog:
      • Delimiter: enter Power (case-sensitive).
      • Start searching from: choose Start of string.
      • End delimiter: leave blank (we want all text after "Power").
      • Click OK.

    A new column Text After Delimiter is added:

    Full SentenceText 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.

    1. Go to Add Column tab > Text Column group > Extract > Text Between Delimiters.
    2. In the Text Between Delimiters dialog:
      • Start delimiter: enter Extracting (case-sensitive).
      • End delimiter: enter with (case-sensitive).
      • Start searching from: choose Start of string.
      • Click OK.

    A new column Text Between Delimiters is added:

    Full SentenceText 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.PositionOf and Text.Range (Extract text at a specific position)

    Suppose you want to extract the 5th word from each sentence.

    1. Add a new column: Add Column tab > Custom Column.
    2. In the Custom Column formula 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.Range extracts the text between these positions ( + 1 to exclude the space).

    Name this new column 5th Word.

    Full Sentence5th 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:

    1. Transform tab > Text Column group > Split Column > By Delimiter.
    2. Choose Space as the delimiter and select Split into Rows or Split into Columns.

    This will create new columns Full Sentence.1, Full Sentence.2, ..., Full Sentence.N.

    Method 5: Using Regular Expressions (Regex) with Text.Combine and List.Accumulate

    For advanced users, you can use Regex to extract patterns from text. Suppose you want to extract all words starting with "P".

    1. Add a new column: Add Column tab > Custom Column.
    2. In the Custom Column formula 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.Split splits the sentence into words.
    • List.Accumulate iterates through the words, checking if each word starts with "P" using Text.StartsWith.
    • If true, the word is added to the accumulated list.
    • Text.Combine joins the extracted words with commas.

    Name this new column Words Starting with P.

    Full SentenceWords 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 Text functions (e.g., Text.Contains, Text.StartsWith, Text.EndsWith) to create more sophisticated extraction logic.
    • Use Error Handling (e.g., try-otherwise blocks) to manage errors when extracting text.

    Happy extracting!

    Additional Resources

    Let me know if you have any questions or need further assistance!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.