PowerQuery only brings in 1 column of data

m3z2t

New member
Joined
Oct 12, 2016
Messages
8
Reaction score
0
Points
0
Hi Folks,

Ultimately I'm trying to get from Folder, but it won't work from Excel file either. The files to get are Excel 97-2003 Workbooks with 30 rows of a unnecessary data in column A quoted below. There are a couple blanks, then 2 rows for header data, then 1 more blank before the data starts. PowerQuery will only recognize column A in the files.

*Everything from "Details of Request" down to just before "Media Name" is exclusively column A. The row with "Media Name" and below starts to span out to other columns.

DETAILS OF REQUEST
------------------
ID ID ID
**********************************************************************************
* *
* *
* RECORD WRITER ACTION REPORT KEY *
* PRINT SOON,MC1 OUTPUT DOWN DEST. OTHERS *
* NAME 1 DESC. INVOICE REPORT REQ FLT *
* *
* MEDIA * CLT CLT PROD ALL ESTIMATE ALL PUB ALL *
* DIV REG DIST ADCODE DATE TYPE *
* PERIOD JAN01/15-DEC31/15 FILTER *
* OPTIONS WIDE,CCYYMMDD,DOWN,NOZERO,GRAND,DOWNHEAD,DOWNFIX *
* *
* HEADERS MEDNAME CLINAME PRDNAME *
* ESTNAME CLI PRDCODE *
* MIDLINE ESTNUM *
* ROWS UDEF=E1 UDEF=E2 BHINVMND *
* BHSDATE BHIMN *
* COLUMNS A BHG B BHNET *
* C BHAC D BHHST *
* E BHGST F BHPST *
* G UCOM=E1 H BHIDATE *
* I J *
* K L *
* M N *
* TITLE *
**********************************************************************************
MEDIACLIENTPRODUCT
NAMENAMENAME
INTERACTIVECLIENTPRODUCT
INTERACTIVECLIENTPRODUCT
INTERACTIVECLIENTPRODUCT
 
Hi,

Don't use from folder for Excel files.

Use From File > Excel file.

Select one file and create the steps needed to produce the required output.

This guide will show you how to convert that Query into a function: http://datapigtechnologies.com/blog...ata-from-multiple-excel-files-into-one-table/

It might look something like this:

Code:
(source as text) as table =>
let
    Source = Excel.Workbook(File.Contents(source), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "HeaderStart", each if [Column1] = "MEDIA" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"HeaderStart"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([HeaderStart] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HeaderStart"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([MEDIA] <> "NAME" and [MEDIA] <> null))
in
    #"Filtered Rows1"

I'm using a sheet called, "Sheet2" in every workbook.
 
Last edited:
Hi,

Don't use from folder for Excel files.

Use From File > Excel file.

Select one file and create the steps needed to produce the required output.

This guide will show you how to convert that Query into a function:

It doesn't work from folder OR from file. Same issue either way.
 
Can you share your M?
 
let Source = Excel.Workbook(File.Contents("C:\Users\firstname.lastname\Documents\DDS\Output\MC__1904.xls"), null, true),
#"MC#,1" = Source{[Name="MC#,1904"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"MC#,1",{{"Column1", type text}})
in
#"Changed Type"

Here you go!
 
and what is, "MC#,1904"?

Is it a worksheet name??
 
Create a blank Query.
Call this Query "GetData"
Paste this M into it:

Code:
(source as text, sheet as text) as table =>
let
    Source = Excel.Workbook(File.Contents(source), null, true),
    SourceSheet = Source{[Item=sheet,Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(SourceSheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "HeaderStart", each if [Column1] = "MEDIA" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"HeaderStart"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([HeaderStart] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HeaderStart"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([MEDIA] <> "NAME" and [MEDIA] <> null))
in
    #"Filtered Rows1"

Create a second blank Query.
Call this Query AllData
Paste this into it:

Code:
let
    Source = Folder.Files("[COLOR=#333333][I]C:\Users\firstname.lastname\Documents\DDS\Output[/I][/COLOR]"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path", "Name", "Extension"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "SheetName", each Text.Replace(Text.Replace([Name],[Extension],""), "__", ".,")),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Merged Column",{"Merged", "SheetName"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Data", each GetData([Merged],[SheetName])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom1", "Data", {"MEDIA", "CLIENT", "PRODUCT"}, {"MEDIA", "CLIENT", "PRODUCT"})
in
    #"Expanded Data"

You can then choose to load AllData as a connection only or to a table in the workbook.
 
I'm getting an error at "Added Custom1" - The key didn't match any rows in the table.

It was a valiant effort Comfy and I really appreciate your help. I think at this point it will be best to go the macro route for getting rid of that pesky header causing us problems retrieving the data in the rest of the file.

THANK YOU!
 
Don't give up yet!

That error is because the files or sheetnames you have don't follow the same naming convention as the example you posted.

MC__1904.xls (two underscores)
MC.,1904 (full stop followed by comma)
 
Also slight amendment as I messed up the header names in my sample:

GetData
Code:
(source as text, sheet as text) as table =>
let
    Source = Excel.Workbook(File.Contents(source), null, true),
    SourceSheet = Source{[Item=sheet,Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(SourceSheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "HeaderStart", each if [Column1] = "MEDIA NAME" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"HeaderStart"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([HeaderStart] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HeaderStart"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([MEDIA NAME] <> null))
in
    #"Filtered Rows1"

AllData
Code:
let
    Source = Folder.Files("[I]C:\Users\firstname.lastname\Documents\DDS\Output[/I]"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path", "Name", "Extension"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "SheetName", each Text.Replace(Text.Replace([Name],[Extension],""), "__", ".,")),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Merged Column",{"Merged", "SheetName"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Data", each GetData([Merged],[SheetName])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom1", "Data", {"MEDIA NAME", "CLIENT NAME", "PRODUCT NAME"}, {"MEDIA NAME", "CLIENT NAME", "PRODUCT NAME"})
in
    #"Expanded Data"
 
An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table

Same deal, "Added Custom1" is causing an error.

Well as long as you keep trying, I'll keep trying! Haha :dance: When I first learned about PQ I thought it would solve my problems with having to pull these reports and compile them this way and that. So I am still hopeful for a solution. Otherwise, I'm quite content with a macro to deal with the headers and let PQ take care of the rest.
 
An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table

Same deal, "Added Custom1" is causing an error.

Well as long as you keep trying, I'll keep trying! Haha :dance: When I first learned about PQ I thought it would solve my problems with having to pull these reports and compile them this way and that. So I am still hopeful for a solution. Otherwise, I'm quite content with a macro to deal with the headers and let PQ take care of the rest.

I posted twice, go to the end of page 1 and read that post there as well. The resubmitted code wasn't to fix your error, it was for something else.
 
Back
Top