Forum Discussion
circular dependency --help please
Hi Anonymous
1. copy "dimension" and paste, then i get "dimension2" table,
in this table with only one column, "remove rows"->remove duplicates
2. copy "data" and paste, then i get "data2" table,
in this table, click on the [index] column and select "Remove other columns", then Removed Duplicates
3.in "dimension2" table, add a custome column
Then expand this column
Best Regards
Maggie
Hi Anonymous
You use 'dimension'[dim] to create the bridge table, then create the relationship between 'dimension' and "check" table, so it occurs an error for circular dependency.
More introduction about avoiding circular dependency please read this article.
As a workaround, you could create this bridge table in Edit Queries.
Create a blank query, then paste the following code in Advanced editor
let
Source1 = dimension,
#"Removed Duplicates" = Table.Distinct(Source1),
Source2 = data,
#"Removed Other Columns" = Table.SelectColumns(Source2,{"index"}),
#"Removed Duplicates2" = Table.Distinct(#"Removed Other Columns"),
addcolumn=Table.AddColumn(#"Removed Duplicates", "index", each #"Removed Duplicates2"),
#"Expanded index" = Table.ExpandTableColumn(addcolumn, "index", {"index"}, {"index.index"})
in
#"Expanded index"
Or you can edit the table steps by steps with the UI
1.Query1:
copy and paste the 'dim' table->Removed Duplicates
2.Query2:
copy and paste the 'data' table->click on the [index] column and select "Remove other columns"->Removed Duplicates
3.in Query1:
Add column from Query2 , then expand the column
Best Regards
Maggie
will this table gets refreshed will scheduled in prod?. I mean if the values (attributes)gets added to the dimension ( productname say) will this data be also reflects to the bridge table automatically without manual intervension?
one more question : if i paste the query using query editor will this query creates other two tales and then join ?. Will this step refreshes the data in bridge table based on data change other two tables ?
3.in Query1:
Add column from Query2 , then expand the column .
can you send me screenshot how you did this from query editor . I am not able to add column from another table .can you help here ?
hi v-juanli-msft if you used custom calculation tab for adding column .if yes can you share code please?
Hi Anonymous
1. when you add values in "dimension" or "data" table and refresh the dataset by clicking "Refresh All", it can automatically refresh in the "Query1".
2.
my first method is:
create a blank query-"Query1"
open the Advanced editor, paste my code, this will create a table which you want.
this new table "Query1" has joined "dimension" or "data" table together.
Yes, when "dimension" or "data" table refresh, table "Query1" will refresh automatically as shown above.
If you don't want table "Query1" to refresh upon the "dimension" or "data" table refresh,
you could uncheck "include in report refresh", then "close &&apply" , go to the "Data view",
then when the "dimension" or "data" table refresh, table "Query1" won't refresh but remain the previous values. (in Data view and Report view)
If you use "Query1" and "Query2" together to create the bridge table, you need to uncheck "include in report refresh" for both "Query1" and "Query2" .
Best Regards
Maggie
hi v-juanli-msft : can you provide steps for add column please