Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Alicia_Anderson
Resolver I
Resolver I

Unable to combine data - Power BI Service Refresh Error

 
The report runs fine in Power BI Desktop but when I publish it to the service it errors.  I feel I have tried about everything but nothing works.    

1) I made sure all my data sources privacy settings were set the same: Organizational.

2) I have my Privacy settings in Power BI Desktop set to "Ignore the Privacy Levels..."

3) I attempted to combine my function into the main query so that it is in one query.

Someone suggested that I rewrite the query so that there is only 1 LET and 1 IN but I don't know how to do that with the function.  Can someone help me? 

 

Alicia_Anderson_0-1699908916988.png

 

let
GetBuildInfo =
(VarBld as text)=>
let
Source = Csv.Document(Web.Contents("http://tw-cm-util-01:8080/job/Build_TWEHR_24.1.0_Main_Scheduled/", [RelativePath=VarBld & "/consoleText"]), [Delimiter="^", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Add CS Warning" = Table.AddColumn(Source, "CS_Warning", each if Text.Contains([Column1], " warning CS") then 1 else null),
#"Filter CS Warnings" = Table.SelectRows(#"Add CS Warning", each ([CS_Warning] = 1)),
#"Add Warning ID" = Table.AddColumn(#"Filter CS Warnings", "Warning ID", each Text.BetweenDelimiters([Column1], " warning ", "["), type text),
#"Add Path" = Table.AddColumn(#"Add Warning ID", "Path", each Text.BetweenDelimiters([Column1], "[", "]", {0, RelativePosition.FromEnd}, 0), type text),
#"Add Project Name" = Table.AddColumn(#"Add Path", "ProjectName", each Text.AfterDelimiter([Path], "\", {0, RelativePosition.FromEnd}), type text)
in
#"Add Project Name",
// Combined function into the same query to avoid Power BI Refresh issue "Unable to combine data"
// Get Build Numbers from PI_Ref to feed into function
Source = CS_Warnings_Source,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PI", type text}, {"Sprint", type text}, {"Yr_Sprint", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"End Date", Order.Ascending}}),
// Eng-Canada locale will set to yyyy-mm-dd
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Start Date", type text}, {"End Date", type text}}, "en-CA"),
#"Add # to Build" = Table.AddColumn(#"Changed Type with Locale", "BuildNo", each "#"& Number.ToText([Build])),
#"Add Bld_Date_Spr" = Table.AddColumn(#"Add # to Build", "Bld_Date_Spr", each Text.Combine({[BuildNo], [End Date], [Sprint]}, " "), type text),
#"Removed Other Columns" = Table.SelectColumns(#"Add Bld_Date_Spr",{"Build", "Bld_Date_Spr"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Build] <> null)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Build", type text}}),
#"Appended Query" = Table.Combine({#"Changed Type1", Last_Success_Build}),
#"Add Build Info" = Table.AddColumn(#"Appended Query", "Build Info", each if [Bld_Date_Spr] is null then "#" & [Build] & " Last-Build" else [Bld_Date_Spr]),
#"Removed Columns" = Table.RemoveColumns(#"Add Build Info",{"Bld_Date_Spr"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "URL", each GetBuildInfo([Build])),
#"Expanded URL" = Table.ExpandTableColumn(#"Invoked Custom Function", "URL", {"Column1", "CS_Warning", "Warning ID", "Path", "ProjectName"}, {"Column1", "CS_Warning", "Warning ID", "Path", "ProjectName"})
in
#"Expanded URL"

 

1 ACCEPTED SOLUTION
v-tianyich-msft
Community Support
Community Support

Hi @Alicia_Anderson ,

 

During my investigation I have found users in the past who have had similar experiences to you .
The solution given by other forum users was: You need to combine all the queries into one query.

 

For original post, you may refer to:Solved: Unable to Combine Data Refresh Error - Microsoft Fabric Community

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-tianyich-msft
Community Support
Community Support

Hi @Alicia_Anderson ,

 

During my investigation I have found users in the past who have had similar experiences to you .
The solution given by other forum users was: You need to combine all the queries into one query.

 

For original post, you may refer to:Solved: Unable to Combine Data Refresh Error - Microsoft Fabric Community

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

aj1973
Community Champion
Community Champion

Hi @Alicia_Anderson 

This combination in the M code is not correct or not recognized in the service

aj1973_0-1699909391218.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@aj1973 This is not true.

 

I tested this query in the Power BI Service and it works fine.

let
  Source = Csv.Document(Web.Contents("https://people.sc.fsu.edu/~jburkardt/data/csv/addresses.csv"))
in
  Source

@AlexisOlson how about you share with us the truth for this specific case !? I am thrilled to know it 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

This is a difficult question to resolve without a test case to work with.

The best I can do is point at some useful articles:
https://www.thepoweruser.com/2019/03/12/data-privacy-and-the-formula-firewall/

https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors