cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eskyline Member
Member

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

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.

Post6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Post7.png

 

 

 

 

 

 

 

 

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.

 

 

 

 

 

3 REPLIES 3
eskyline Member
Member

How to extract a particular cell in xls and put it to the rows?

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.

Post6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Post7.png

 

 

 

 

 

 

 

 

 

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.

 

 

 

 

Moderator v-qiuyu-msft
Moderator

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

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
eskyline Member
Member

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

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.

RefreshNowError.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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

ScheduleRefreshError.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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

QueriesShot.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Gateway def.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Data Source Settings.png

 

 

 

 

 

 

 

 

 

 

 

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.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)