Unable to combine data - Power BI Service Refresh Error

[Alicia_Anderson]

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? 

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"

[v-tianyich-msft · Accepted solution]

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]

Hi @Alicia_Anderson 

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

[AlexisOlson]

@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

[aj1973]

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

[AlexisOlson]

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/