Importing data into Power BI from Excel

[MitieNHSFred]

I’ve been sent an Excel workbook with 3 identical worksheets, each representing a review at different project stages.

Each sheet has 4 titled 10 question “sections” , each section is separated by a blank row.  Each question has an associated 1, 2, 3 (Low, Medium, High) score. Finally, there is a Key “section”, which is separated from the final Q&A section by 2 blank rows, which I don't need.

The Section titles and associated 10 questions are all in column A, with the scores in column B.

I tried a straight “Import data”, which recognised the 3 worksheets and gave me suggested Tables for each worksheet, however it only recognises the first ”section” header and the Key “section”, ignoring the other "section" headers completely and treating all rows, apart from those in the Key, as part of the first section.

The imported data has all 40 questions from the 4 sections plus their associated scores.

I tried putting an additional blank row between each of the sections but that changed nothing when I re-tried the import

Any suggestions as to how I get the import to recognise the intermediate sections would be much appreciated.

Thanks and regards

Fred

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[Thejeswar]

Hi @MitieNHSFred ,

Same here as @danextian . All this is trying to fix something that is not even visible to have an idea of what to fix. Please also consider sharing how the final data is supposed to look post importing the data.

Apart from sharing an excel file, another place that you can start looking into is the Power Query editor on what is happening in the steps there that removes the blank rows between sections.

Regards,

[MitieNHSFred]

Apologies both ( @danextian ), but it's a very basic spreadsheet, example table and screenshot below, hopefully this helps 

Column A	Column B
Section 1 (of 4)
Question 1	Score (1-3)
Question 2 (of 10)	Score (1-3)
	Total
blank row
Section 2 (of 4)
Question 1	Score (1-3)
Question 2 (of 10)	Score (1-3)
Screenshot of spreadsheet page

[danextian]

The screenshot is missing. You can also just past the data and give us a screenshot of your expected result made up in Excel.

[MitieNHSFred]

I don't want the result in Excel, I need to import the data from Excel into Power BI, where it can be used to generate a report (format yet to be determined) as part of an ExCo report pack.

I need the Import Data to recognise that there are 4 separate sections and store the questions/scores accordingly, rather than putting it all undet the first section.

When I import to Power BI I get the following, whereas I want to get "Suggested Tables" Section 1 (PCT 1), Section 2 (PCT 1), Section 3 (PCT 1), Section 4 (PCT 1) and the same for PCT2 and PCT3, but these don't appear

[danextian]

You wil need to create a cusotm column checking whether Column A contains the word Section (case-sensitive) and if true, return Column A else null. Then fill down that custom and then remove empty/blank rows from Column A.

Try this M Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk5NLsnMzzNU0lFSitWJVgosTS0GCSiARAxRhYwUNPLTFAwNNIFSRmApICMkvyQxB8YB01AjFYzIMDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Section", each if Text.Contains([Column A],"Section") then [Column A] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Section"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column A], "Section")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column A] <> "" and [Column A] <> null),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Column A", "Question"}})
in
    #"Renamed Columns"

This transformation needs to be applied to each worksheet. However, since I don't have a similar file to work with, I can only provide a code that works for a single sheet.

[MitieNHSFred]

Wow, not even gonna beging to wonder what this does, thnkyou.

The 3 worksheets are identical, so if it works for one it will work for all without any changes.

That said, howver, the current M code looks like this, where should I position your code please ?

let
Source = Excel.Workbook(File.Contents("C:\Users\FJN\MyDrive - Our Company\Documents\PCT Assessment Blank Template.xlsx"), null, true),
#"PCT 1_Sheet" = Source{[Item="PCT 1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"PCT 1_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project PCT Assessment 1", type text}, {"Column2", Int64.Type}, {"Column3", type any}})
in
#"Changed Type"

However this is the M code for Section 1 (PCT1), not sure which code needs your update

let
Source = Excel.Workbook(File.Contents("C:\Users\FJN\My Drive - Our Company\Documents\PCT Assessment Blank Template.xlsx"), null, false),
#"PCT 1_sheet" = Source{[Item="PCT 1",Kind="Sheet"]}[Data],
FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")),
#"Removed Top Rows" = Table.Skip(#"PCT 1_sheet", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows", List.Select(Table.ColumnNames(#"Removed Top Rows"), each try not List.IsEmpty(FilterNullAndWhitespace(Table.Column(#"Removed Top Rows", _))) otherwise true)),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(FilterNullAndWhitespace(Record.FieldValues(_)))),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Column1", type text}, {"Column2", Int64.Type}})
in
#"Changed Type"

[danextian · Accepted solution]

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.

[danextian]

Hi @MitieNHSFred 

It is honestly difficult to provide a working or almost working code with just your description. Think about being in our shoes and us describing to you the file but without even seeing it 😄. That wouldn't be cool. Start with providing a link to your Excel file  stored in the cloud with confidential data removed but still actually represents the structure of youe data, your expected result from the same sample data and the reasoning behind.

[MitieNHSFred]

As to the desired result ( @Thejeswar ) I don't know, as I want to get the data into Power BI, then I can work on what to do with it.

[danextian]

A link to your sample data please because who wants to be manually typing those texts. As to the desired result, you can just make it up in Excel.

[MitieNHSFred]

unfortuantely I am unable to share as company policy blocks sharing files outside our network.