PowerQuery only brings in 1 column of data

m3z2t · Oct 12, 2016

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 *
**********************************************************************************
MEDIA CLIENT PRODUCT
NAME NAME NAME
INTERACTIVE CLIENT PRODUCT
INTERACTIVE CLIENT PRODUCT
INTERACTIVE CLIENT PRODUCT

Comfy · Oct 12, 2016

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.

m3z2t · Oct 12, 2016

Comfy said:
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.

Comfy · Oct 12, 2016

Can you share your M?

m3z2t · Oct 12, 2016

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!

Comfy · Oct 12, 2016

and what is, "MC#,1904"?

Is it a worksheet name??

m3z2t · Oct 12, 2016

MC.,1904 is the worksheet name.

Comfy · Oct 12, 2016

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.

m3z2t · Oct 12, 2016

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!

Comfy · Oct 12, 2016

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)

Comfy · Oct 12, 2016

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"

m3z2t · Oct 12, 2016

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.

Comfy · Oct 12, 2016

m3z2t said:
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.

PowerQuery only brings in 1 column of data

m3z2t

New member

Comfy

New member

m3z2t

New member

Comfy

New member

m3z2t

New member

Comfy

New member

m3z2t

New member

Comfy

New member

m3z2t

New member

Comfy

New member

Comfy

New member

m3z2t

New member

Comfy

New member

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 *
**********************************************************************************


MEDIA	CLIENT	PRODUCT
NAME	NAME	NAME

INTERACTIVE	CLIENT	PRODUCT
INTERACTIVE	CLIENT	PRODUCT
INTERACTIVE	CLIENT	PRODUCT