Creating new blank query by detecting relationship

[jeongkim]

Hi,

I'm creating new blank query but it seems it is checking relationship, eventually got error, which how can this be? 

Since it is being created as new. 

But the query script was copied and adjusted from other table. 

Data is loaded well in query but not being applied..

[v-sdhruv]

Hi @jeongkim ,

Could you please help us with specifying the error you are getting so that we can assist you better and help you with the issue.

Thank you.

[v-sdhruv]

Hi @jeongkim ,
Can you please specify the error that your getting?
Maybe that would give us some clarity as to which table its pointing to.
Thank you @Ritaf1983  for your suggestion.

[burakkaragoz]

Hi @jeongkim ,

yeah, that can happen sometimes when you copy a query from another table — even if it’s a “new” query, Power BI might still try to auto-detect relationships based on the structure or column names.

a few things you can try:

• go to File > Options and settings > Options > Current File > Data Load and uncheck “Autodetect new relationships after data is loaded” — this might stop Power BI from trying to force relationships during load.
• double-check if any relationships were created automatically in the model view — if yes, delete them and try loading again.
• if the query has any columns with the same names as existing tables, Power BI might assume they’re related — try renaming them slightly to avoid that.

also, make sure the query doesn’t have any steps referencing other queries or tables — even indirectly — that might cause issues during load.

let me know if you want to share the query steps, I can help take a closer look.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
*This response was supported by AI for translation and text editing purposes.

[jeongkim]

• double-check if any relationships were created automatically in the model view — if yes, delete them and try loading again.

How could this happen before new table created? relationships can be built after tables are made, isn't it? 

[jeongkim]

Hi, Thanks for help.

Since new table trying to create is located in same SP and parent folder location, I copied from below other table. 

This code is other table code which error table copied and made some changes:

let
Source = SharePoint.Contents("https://xxxxx.sharepoint.com/sites/xxxxx", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR Cell Status" = #"Shared Documents"{[Name="KR Cell Status"]}[Content],
#"Filtered Rows CT" = Table.SelectRows(#"KR Cell Status", each ([Name] = "KT")),

#"Listed Columns CT" = Table.ColumnNames(#"Filtered Rows CT"[Content]{0}),
#"Expanded Custom CT" = Table.ExpandTableColumn(#"Filtered Rows CT", "Content", #"Listed Columns CT", List.Transform(#"Listed Columns CT", each "Custom/" & _)),
#"Filtered Rows PM Data - KPI Trend" = Table.SelectRows(#"Expanded Custom CT", each ([#"Custom/Name"] = "PM Data - KPI Trend")),
#"Listed Columns PM Data - KPI Trend" = Table.ColumnNames(#"Filtered Rows PM Data - KPI Trend"[#"Custom/Content"]{0}),
#"Expanded Custom PM Data - KPI Trend" = Table.ExpandTableColumn(#"Filtered Rows PM Data - KPI Trend", "Custom/Content", #"Listed Columns PM Data - KPI Trend", List.Transform(#"Listed Columns PM Data - KPI Trend", each "Custom." & _)),
#"Filtered Rows <> null" = Table.SelectRows(#"Expanded Custom PM Data - KPI Trend", each ([Custom.Name] <> null)),
#"Listed Columns File" = Table.ColumnNames(#"Filtered Rows <> null"[Custom.Content]{0}),
#"Expanded Custom File" = Table.ExpandTableColumn(#"Filtered Rows <> null", "Custom.Content", #"Listed Columns File", List.Transform(#"Listed Columns File", each "Custom-" & _)),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom File", each ([#"Custom-Name"] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Custom-Content", "Custom-Name", "Custom-Date created", "Custom.Name", "Name"}),
#"Added Year" = Table.AddColumn(#"Removed Other Columns", "Year", each Text.Start([#"Custom-Name"], 4)),
#"Added Week" = Table.AddColumn(#"Added Year", "Week", each Text.Middle([#"Custom-Name"], 6, 2)),
#"Added Period" = Table.AddColumn(#"Added Week", "Period", each [Year] & [Week]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Period",{{"Custom-Date created", type date}, {"Week", Int64.Type}, {"Period", Int64.Type}, {"Year", Int64.Type}}),
#"Added Month" = Table.AddColumn(#"Changed Type", "Month", each if [Week] > 48 then 12 else if [Week] > 44 then 11 else if [Week] > 39 then 10 else if [Week] > 35 then 9 else if [Week] > 31 then 8 else if [Week] > 26 then 7 else if [Week] > 22 then 6 else if [Week] > 18 then 5 else if [Week] > 13 then 4 else if [Week] > 9 then 3 else if [Week] > 5 then 2 else if [Week] >= 1 then 1 else null),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Month",{{"Month", Int64.Type}}),
#"Sorted Rows Descending Period" = Table.Sort(#"Changed Type2",{{"Period", Order.Descending}}),

#"Distinct Period" = Table.Distinct(Table.SelectColumns(#"Sorted Rows Descending Period", {"Period"})),
#"Top N= 104 Period" = Table.FirstN(#"Distinct Period", 104),
#"Joined Period and File" = Table.NestedJoin(#"Sorted Rows Descending Period", {"Period"}, #"Top N= 104 Period", {"Period"}, "Period Mapping", JoinKind.Inner),

#"Added Weekly" = Table.AddColumn(#"Joined Period and File", "Weekly", each if [Year] = Date.Year(DateTime.LocalNow()) and [Week] >= Date.WeekOfYear(DateTime.LocalNow()) - 40 then "Keep" else "Remove"),
#"Added Monthly" = Table.AddColumn(#"Added Weekly", "Monthly", each if [Week] = 5 or [Week] = 9 or [Week] = 13 or [Week] = 18 or [Week] = 22 or [Week] = 26 or [Week] = 31 or [Week] = 35 or [Week] = 39 or [Week] = 44 or [Week] = 48 or [Week] = 52 then "Keep" else "Remove"),
#"Merged Weekly Monthly" = Table.AddColumn(#"Added Monthly", "Weekly/Monthly", each [Weekly] & " " & [Monthly]),
#"Split Column by Delimiter Frequency | Region" = Table.SplitColumn(#"Merged Weekly Monthly", "Custom.Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Custom.Name.1", "Custom.Name.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter Frequency | Region", each ([Custom.Name.1] = "LTE")),
#"Filtered Rows <> Remove Remove" = Table.SelectRows(#"Filtered Rows", each ([#"Weekly/Monthly"] <> "Remove Remove")),
#"Add Custom Table for Sheet"= Table.AddColumn(#"Filtered Rows <> Remove Remove", "Custom", each Excel.Workbook([#"Custom-Content"])),
#"Listed Columns Sheet" = Table.ColumnNames(#"Add Custom Table for Sheet"[Custom]{0}),
#"Expanded Custom Sheet" = Table.ExpandTableColumn(#"Add Custom Table for Sheet", "Custom", #"Listed Columns Sheet", List.Transform(#"Listed Columns Sheet", each "Custom/" & _)),
#"Filtered Rows 2nd Sheet" = Table.SelectRows(#"Expanded Custom Sheet", each Text.Contains([#"Custom/Name"], "Documentation")),
#"Listed Columns Value" = Table.ColumnNames(#"Filtered Rows 2nd Sheet"[#"Custom/Data"]{0}),
#"Expanded Custom Value" = Table.ExpandTableColumn(#"Filtered Rows 2nd Sheet", "Custom/Data", #"Listed Columns Value", List.Transform(#"Listed Columns Value", each "Custom^" & _)),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom Value",{"Custom-Content", "Period Mapping", "Custom/Item", "Custom/Kind", "Custom/Hidden", "Custom/Name", "Custom^Column2", "Weekly/Monthly"}),
Custom1 = List.PositionOf(
Table.Column(#"Removed Columns", "Custom^Column1"),
"KPI ID"
),
#"Removed Top Rows" = Table.Skip(#"Removed Columns", Custom1),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns Fixed" = Table.RenameColumns(#"Promoted Headers1",
{{Table.ColumnNames(#"Promoted Headers1"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers1"){1}, "Date Created"},
{Table.ColumnNames(#"Promoted Headers1"){2}, "Frequency"},
{Table.ColumnNames(#"Promoted Headers1"){3}, "Region"},
{Table.ColumnNames(#"Promoted Headers1"){4}, "CT"},
{Table.ColumnNames(#"Promoted Headers1"){5}, "Year"},
{Table.ColumnNames(#"Promoted Headers1"){6}, "Week"},
{Table.ColumnNames(#"Promoted Headers1"){7}, "Period"},
{Table.ColumnNames(#"Promoted Headers1"){8}, "Month"},
{Table.ColumnNames(#"Promoted Headers1"){9}, "Weekly"},
{Table.ColumnNames(#"Promoted Headers1"){10}, "Monthly"}}),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns Fixed", each ([KPI Title] <> null and [KPI Title] <> "KPI Title"))
in
#"Filtered Rows2"

and this one is getting error: 

let
Source = SharePoint.Contents("https://xxxxx.sharepoint.com/sites/xxxxx", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR Cell Status" = #"Shared Documents"{[Name="KR Cell Status"]}[Content],
#"Filtered Rows CT" = Table.SelectRows(#"KR Cell Status", each [Name] = "KT" or [Name] = "SKT" or [Name] = "LGU+"),

#"Listed Columns CT" = Table.ColumnNames(#"Filtered Rows CT"[Content]{0}),
#"Expanded Custom CT" = Table.ExpandTableColumn(#"Filtered Rows CT", "Content", #"Listed Columns CT", List.Transform(#"Listed Columns CT", each "Custom/" & _)),
#"Filtered Rows FM Data - Alarm Trend" = Table.SelectRows(#"Expanded Custom CT", each ([#"Custom/Name"] = "FM Data - Alarm Trend")),
#"Listed Columns FM Data - Alarm Trend" = Table.ColumnNames(#"Filtered Rows FM Data - Alarm Trend"[#"Custom/Content"]{0}),
#"Expanded Custom FM Data - Alarm Trend" = Table.ExpandTableColumn(#"Filtered Rows FM Data - Alarm Trend", "Custom/Content", #"Listed Columns FM Data - Alarm Trend", List.Transform(#"Listed Columns FM Data - Alarm Trend", each "Custom." & _)),
#"Filtered Rows <> null" = Table.SelectRows(#"Expanded Custom FM Data - Alarm Trend", each ([Custom.Name] <> null)),
#"Listed Columns File" = Table.ColumnNames(#"Filtered Rows <> null"[Custom.Content]{0}),
#"Expanded Custom File" = Table.ExpandTableColumn(#"Filtered Rows <> null", "Custom.Content", #"Listed Columns File", List.Transform(#"Listed Columns File", each "Custom-" & _)),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom File", each ([#"Custom-Name"] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Custom-Content", "Custom-Name", "Custom-Date created", "Custom.Name", "Name"}),
#"Added Year" = Table.AddColumn(#"Removed Other Columns", "Year", each Text.Start([#"Custom-Name"], 4)),
#"Added Week" = Table.AddColumn(#"Added Year", "Week", each Text.Middle([#"Custom-Name"], 6, 2)),
#"Added Period" = Table.AddColumn(#"Added Week", "Period", each [Year] & [Week]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Period",{{"Custom-Date created", type date}, {"Week", Int64.Type}, {"Period", Int64.Type}, {"Year", Int64.Type}}),
#"Added Month" = Table.AddColumn(#"Changed Type", "Month", each if [Week] > 48 then 12 else if [Week] > 44 then 11 else if [Week] > 39 then 10 else if [Week] > 35 then 9 else if [Week] > 31 then 8 else if [Week] > 26 then 7 else if [Week] > 22 then 6 else if [Week] > 18 then 5 else if [Week] > 13 then 4 else if [Week] > 9 then 3 else if [Week] > 5 then 2 else if [Week] >= 1 then 1 else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Month",{{"Month", Int64.Type}}),
#"Sorted Rows Descending Period" = Table.Sort(#"Changed Type1",{{"Period", Order.Descending}}),

#"Distinct Period" = Table.Distinct(Table.SelectColumns(#"Sorted Rows Descending Period", {"Period"})),
#"Top N= 104 Period" = Table.FirstN(#"Distinct Period", 104),
#"Filtered Rows Period >= 25R1 Profile Data" = Table.SelectRows(#"Top N= 104 Period", each [Period] >= 202511),
#"Joined Period and File" = Table.NestedJoin(#"Sorted Rows Descending Period", {"Period"}, #"Filtered Rows Period >= 25R1 Profile Data", {"Period"}, "Period Mapping", JoinKind.Inner),

#"Added Weekly" = Table.AddColumn(#"Joined Period and File", "Weekly", each if [Year] = Date.Year(DateTime.LocalNow()) and [Week] >= Date.WeekOfYear(DateTime.LocalNow()) - 4 then "Keep" else "Remove"),
#"Added Monthly" = Table.AddColumn(#"Added Weekly", "Monthly", each if [Week] = 5 or [Week] = 9 or [Week] = 13 or [Week] = 18 or [Week] = 22 or [Week] = 26 or [Week] = 31 or [Week] = 35 or [Week] = 39 or [Week] = 44 or [Week] = 48 or [Week] = 52 then "Keep" else "Remove"),
#"Merged Weekly Monthly" = Table.AddColumn(#"Added Monthly", "Weekly/Monthly", each [Weekly] & " " & [Monthly]),
#"Filtered Rows <> Remove Remove" = Table.SelectRows(#"Merged Weekly Monthly", each ([#"Weekly/Monthly"] <> "Remove Remove")),
#"Add Custom Table for Value"= Table.AddColumn(#"Filtered Rows <> Remove Remove", "Custom", each Csv.Document([#"Custom-Content"])),
#"Listed Columns Value" = Table.ColumnNames(#"Add Custom Table for Value"[Custom]{0}),
#"Expanded Custom Value" = Table.ExpandTableColumn(#"Add Custom Table for Value", "Custom", #"Listed Columns Value", List.Transform(#"Listed Columns Value", each "Custom/" & _)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom Value", "Custom.Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Custom.Name.1", "Custom.Name.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom-Content", "Period Mapping", "Weekly/Monthly"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Renamed Columns Fixed Header" = Table.RenameColumns(#"Promoted Headers",
{
{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Date Created"},
{Table.ColumnNames(#"Promoted Headers"){2}, "Frequency"},
{Table.ColumnNames(#"Promoted Headers"){3}, "Region"},
{Table.ColumnNames(#"Promoted Headers"){4}, "CT"},
{Table.ColumnNames(#"Promoted Headers"){5}, "Year"},
{Table.ColumnNames(#"Promoted Headers"){6}, "Week"},
{Table.ColumnNames(#"Promoted Headers"){7}, "Period"},
{Table.ColumnNames(#"Promoted Headers"){8}, "Month"},
{Table.ColumnNames(#"Promoted Headers"){9}, "Weekly"},
{Table.ColumnNames(#"Promoted Headers"){10}, "Monthly"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns Fixed Header",{"Source Name", "Date Created", "Frequency", "Region", "CT", "Year", "Week", "Period", "Month", "Weekly", "Monthly", "DN", "Alarm number", "Severity", "Alarm Time", "Cancel Time", "Alarm text", "Supplementary information", "Diagnostic information", "Probable cause", "User Additional Information"}),
Custom1 = Table.SelectRows(#"Removed Other Columns1", each ([DN] <> "" and [DN] <> "DN")),
#"Added Key dn" = Table.AddColumn(Custom1, "Key dn", each Text.BeforeDelimiter([DN], "/", 1)),
#"Added DU ID" = Table.AddColumn(#"Added Key dn", "DU ID", each Text.AfterDelimiter([#"Key dn"], "-", 1)),

#"Added Index" = Table.AddIndexColumn(#"Added DU ID", "Index", 1, 1, Int64.Type),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Index",{{"DU ID", Int64.Type}, {"Alarm number", Int64.Type}, {"Alarm Time", type datetime}, {"Cancel Time", type datetime}})
in
#"Changed Type2"

[Ritaf1983]

Hi @jeongkim 

Unfortunately, from the screenshots it's unclear whether the error is related to the query itself or the relationships.

A. The actual failing table is not shown — please scroll down the load error pane to identify the specific table that failed. The messages currently displayed only show that loading failed, not which query or table caused it.

B. If the issue is indeed caused by Power BI trying to automatically detect and create relationships (which can sometimes fail if the key columns are incompatible), you can disable this behavior:
Go to File > Options and settings > Options > Current File > Data Load, and uncheck the “Auto-detect new relationships after data is loaded” setting.

Once disabled, you can manage relationships manually to ensure compatibility.

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