How to extract a particular cell in xls, get it to each row, and have it refresh in the service?

[eskyline]

He everyone.

I’m wondering how to get the merged cell A3-C3 (highlighted below) out of the column and into each row.

Here is the data I have in an xls.

Here is how I want it to be in the imported model for reporting off of.

I’ve see instances where people duplicate the column (in this case the merged column containing “Tier 1 – Charity”) and then filter that new column to get just what they want.  That won’t work in this particular case because there is nothing specific in that text that differentiates that data from the actual user last name that would come along with it.

My end goal is use Get Data on a “Folder” where the user can dump multiple XLS’s each with a different “template” (value of merged cell A3-C3), have Power BI read them all in, make them row based, and then be able to report on items by “template”.  I need this to refresh via the Power BI Service and the On-Prem data gateway.

I did write some custom M that would extract the logic, converting all the Power Query steps I did for the one file, including the custom M, and this worked in the desktop but when I published it to the Power BI Service I was unable to schedule a refresh on the dataset because of the function.  I looked at https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i... but was unable to figure out how to get this to work in my scenario with Excel as opposed to a URL.

Any help is greatly appreciated.

[eskyline]

He everyone, I’m wondering how to get the merged cell A3-C3 (highlighted below) out of the column and into each row.

Here is the data I have in the xls.

I'd like it to appear as follows, row based with the template name in the first column.

I’ve see instances where people duplicate the column in Power BI/Power Query (in this case the merged column containing “Tier 1 – Charity”) and then filter that new column to get just the cell they want, then do a fill down or up to fill each row. That won’t work in this particular case because there is nothing specific in that text that differentiates that data from the actual user last name that would come along with it.

My end goal is use Get Data on a “Folder” where the user can dump multiple XLS’s each with a different “template” (value of merged cell A3-C3), have Power BI read them all in, make them row based, and then be able to report on items by “template”. I need this to refresh via the Power BI Service and the On-Prem data gateway.

I did write some custom M that would extract the logic, converting all the Power Query steps I did for the one file, including the custom M, and this worked in the desktop but when I published it to the Power BI Service I was unable to schedule a refresh on the dataset because of the function. I looked at https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i... but was unable to figure out how to get this to work in my scenario with Excel as opposed to a URL.

Any help is greatly appreciated.

[v-qiuyu-msft]

Hi @eskyline,

Which error throws out when you refresh in the service? If those Excel files stores in local computer, please install the data gateway on the same server. Also create the Folder data source under data gateway as created in the desktop.

If issue persists, can you share the sample folder and .pbix file to show how you use Power Query, so that we can try to reproduce on our side?

Best Regards,
Qiuyun Yu

[eskyline]

Qiuyun,

Hopefully this helps.  If not I can try to attach the PBIX.

Here is the error I receive when I try to refresh the dataset in the service.

Here is the message I get when I try to "Schedule a refresh" in the service on the dataset

Here is the list of queries I have in the Query Editor window

Here is the actual M script for each of the queries.

// FileName
"SomeFile.xlsx" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

// FilePath
"C:\Users\xxxxxx\Documents\Power BI local drive\Test Reports\TestData\" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

// Full Report
let
 Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true),
 #"Full Report_Sheet" = Source{[Item="Full Report",Kind="Sheet"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(#"Full Report_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
   
 #"Removed Top Rows" = Table.Skip(#"Changed Type",2), 
 #"TestName" = Record.Field(#"Removed Top Rows"{0},"Column1"),
 #"TestDate" = Record.Field(#"Removed Top Rows"{0},"Column4"),
 #"Removed Top Rows1" = Table.Skip(#"Removed Top Rows",3),
 #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
 #"x" = Table.AddColumn(#"Promoted Headers", "TestName", each #"TestName"),
 #"y" = Table.AddColumn(#"x", "TestDate", each TestDate),
 #"Filtered Rows" = Table.SelectRows(y, each [Last Name] <> null)

in
    #"Filtered Rows"

// CombinedData
let
    Source = Folder.Files("\\uxxxxx\uxxxxxa\xxxxd\sxxx5\powerbi\it\xxxxx"),  -- NOTE I x'ed some of this out, its valid in the pbix
    #"Invoked Custom Function" = Table.AddColumn(Source, "ProcessOneFile", each ProcessOneFile([Name], [Folder Path])),
    #"Expanded ProcessOneFile" = Table.ExpandTableColumn(#"Invoked Custom Function", "ProcessOneFile", {"Last Name", "First Name", "Sub-Group", "Not Clicked /Clicked", "Email ", "Action Taken", "TestName", "TestDate"}, {"ProcessOneFile.Last Name", "ProcessOneFile.First Name", "ProcessOneFile.Sub-Group", "ProcessOneFile.Not Clicked /Clicked", "ProcessOneFile.Email ", "ProcessOneFile.Action Taken", "ProcessOneFile.TestName", "ProcessOneFile.TestDate"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ProcessOneFile", "Index", 0, 1)
in
    #"Added Index"

// ProcessOneFile
let
    Source = (FileName as any, FilePath as any) => let
    Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true),
    #"Full Report_Sheet" = Source{[Item="Full Report",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Full Report_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"TestName" = Record.Field(#"Removed Top Rows"{0},"Column1"),
    #"TestDate" = Record.Field(#"Removed Top Rows"{0},"Column4"),
    #"Removed Top Rows1" = Table.Skip(#"Removed Top Rows",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
    #"x" = Table.AddColumn(#"Promoted Headers", "TestName", each #"TestName"),
    #"y" = Table.AddColumn(#"x", "TestDate", each TestDate),
        #"Filtered Rows" = Table.SelectRows(y, each [Last Name] <> null)
in
    #"Filtered Rows"
in
    Source

Here is the gateway defintion I have.  We are using the OnPrem data gateway, not the personal gateway.  The defiintion points to a folder on a network share.

Here is the data source settings in the pbix file (pointing the same folder as the gateway def)

The PBIX will refresh fine in the Power BI Desktop from this network share.  Its only after I've published it and try to refresh it via the onprem data gateway that I have an issue.

I suspected it was the "Web.contents" call in the Full Report script that was the issue based off some other things I saw on the internet.  I'm not sure that is the case or how to get around that. 

// Full Report
let
 Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true),

Thanks for taking a look.  I really appreciate any information you can provide.