Importing data into Power BI from Excel

Your solution is so great danextian 

Hi, MitieNHSFred 

Based on what you said earlier, I've created a simple dataset:

To make it easier for Power BI to do your analysis, you can indeed create a table like the one shown by Super user. Here are the steps:

After adding the condition column, the result should look something like this:

Next, filter out the unnecessary rows of columnA:

Finally, the results available for Power BI are as follows:

In this way, you can easily create dimensional analyses for different sections:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi MitieNHSFred ,

Please see the code below with comments. I added a portion of my code to after the Changed Type applied step. You will need to [Column A] to the actual column name. Once done, duplicate it and then in the duplicate,  replace "PCT 1" with the actual name of the worksheet.

// Extract the data from the "PCT 1" sheet
#"PCT 1_Sheet" = Source{[Item = "PCT 1", Kind = "Sheet"]}[Data],

To  combine these three queries, look into Append in Power Query. You will need to disable the loading of these three staging queries as you already have the final and combined one in the append. Right click a query and uncheck Enable load to do that.

let
  // Load the Excel workbook file
  Source = Excel.Workbook(
    File.Contents("C:\Users\FJN\MyDrive - Our Company\Documents\PCT Assessment Blank Template.xlsx"),
    null,
    true
  ),
  
  // Extract the data from the "PCT 1" sheet
  #"PCT 1_Sheet" = Source{[Item = "PCT 1", Kind = "Sheet"]}[Data],
  
  // Promote the first row to headers
  #"Promoted Headers" = Table.PromoteHeaders(#"PCT 1_Sheet", [PromoteAllScalars = true]),
  
  // Change column data types
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {{"Project PCT Assessment 1", type text}, {"Column2", Int64.Type}, {"Column3", type any}}
  ),
  
  // Add a custom column to identify sections based on "Column A"
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Section",
    each if Text.Contains([Column A], "Section") then [Column A] else null
  ),
  
  // Fill down values in the "Section" column
  #"Filled Down" = Table.FillDown(#"Added Custom", {"Section"}),
  
  // Filter out rows where "Column A" contains the word "Section"
  #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column A], "Section")),
  
  // Filter out blank or null rows in "Column A"
  #"Filtered Rows1" = Table.SelectRows(
    #"Filtered Rows",
    each [Column A] <> "" and [Column A] <> null
  ),
  
  // Rename "Column A" to "Question"
  #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1", {{"Column A", "Question"}})
in
  // Return the final transformed table
  #"Renamed Columns"

FilterNullAndWhitespace in the second code is confusing. It is telling M to select items from a list but doesn't specify the name of the list or an expression that generates a list.