RLS and column values

[Anonymous]

I have a column that looks as follows:

Cost Center

123

234

245

543

345

I would like another new column (in the edit query) from the above to look as follows:

New Column

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

Basically all cost centers in one line serparated with a comma. 

2.Then, I have a seperate table called SETUP :

Cost Center             ID

*                              467773

*                              893774

I would like to replace each * with those concatenated values in the edit query mode. So that each time the first table is refreshed, the copied values in SETUP table also changes accordingly. So my set up table should look like:

Cost Center                                ID

123,234,245,543,345                  467773

123,234,245,543,345                  893774

If a new cost center is added, setup table should be able to recognize that.

Could you please help me with achieving the concatenation in edit query, followed by how to replace * with those concatenated values in the SETUP Table (Another Table)?  Thank you!

[Nathaniel_C]

@Anonymous ,

OK found a replacement for the combine the text.  After transposing, you merge the columns.  That way it will update correctly each month! And that also does away with the filtering to the one row, as it does that automatically. Nice!

Also changes Column = CONCATENATEX(Chec,if(not(isblank(Chec[Merged])),Chec[Merged]))

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

@Anonymous ,

So one more change if it fits in your process. Last step in PQ is to delete the Cost Center Column. So delete it the first time in PBI too. Then rename your new column to Cost Center. When you refresh the query, it will update everything, and since there is no Cost Center column, it won't overwrite your new one, and will update automatically.

Interesting process, thanks for the chance to work on it!

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

So @Anonymous , 

Figured out one more step to make this totally dynamic.

Transforming the table with the numbers of the stores. 

1. If the source brings the data in as anything other than text, change type.

2.Transpose Table the column will be a row.

3. Change all columns to text.

4. Here is the crux. Insert this step by hitting the FX button. = Table.ColumnNames(#"Changed Type1") and change the name of the step to List of Columns.

5. Insert next step same way. = Table.CombineColumns(#"Changed Type1",#"List of Columns",Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")

Step 4 took a bunch of research into the M language, but it should update every time you refresh with any new stores.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,

Is there any reason that * are there?

So for the first part, I created your table, then transposed the column into rows, then combined the columns into a new column using [Col 1]&", " &[Col 2].  Then transposed the row back into a column and filtered for that one cell. I then added the two tables to PBI.

From there using table as Chec I created

Column = CONCATENATEX(Chec,if(not(isblank(Chec[Column1])),Chec[Column1]))

As there is only the one value, there is not really any concatenation going on. I looked on the web, but couldn't see how to do this any other way.  Will keep looking if this is not the solution. Interesting! You could rename the Column to Cost Center and wouldn't have to bother with *. May have to come up with something that other than the combine columns too.  

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C  Hi Nathaniel!

The reason for the * is just ti indicate they have global access. That's how the management has mentioned it. I am going to go through the steps you have given to check if it works! Thank you!

[Nathaniel_C]

OK, let me know...so are there other values in that column? And only when * does this get replaced by the string?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C  Yes that is right 🙂 .. If say there are 2 cost centers 123344, 443322 and my ID is 678 and I have global access.

The table would look like

Cost Center       ID

*                        678

So, for my rLS to work properly, I want to replace * like:

Cost Center          ID

123344                678

443322               678

in place of the *. 

In my data table there are atleast 70000 cose centers and 30o people who have * and I want to replace the * like the above way. 

[Nathaniel_C]

@Anonymous ,

Sorry, don't understand your  latest. At the beginning you were looking for a concatenated value:

Cost Center                                ID

123,234,245,543,345                  467773

123,234,245,543,345                  893774

[Nathaniel_C]

@Anonymous ,

Started with:

and 

finished with * replaced with concatenated list

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Ok, so we went off line today and walked through all the steps. To recap, we turn one table into a list, and then use it as a new column which can then be used in the second table as the  replacement value of the "*". The crux of the matter was being able to make sure that our list picked up any new Cost Centers. This I mentioned as the crux in a post above.

Finally the one remaining issue was my example data table has one column, and your table has many. So the answer is to create a table from the original table by reference.  This allows us to work with a new table, but one that has a string to old table and will update. I will post the text from my last message to you below.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

===============

OK tested, created a table with multiple columns. Then went to Home, Manage, Reference. This created a new table with links to the old one. We only do this so that we have a new table that we can delete col etc, but what we have left will update. The source for the new table is the old table. I called the new table TestList.

Here is the advanced editor. By hand, after you remove other columns, transpose the table, and change the type, you must insert a custom step by clicking the F(X) button. This is the crux, you have to insert a step that will gather all the column names, so when you add a new center it will pick it up. So F(X) and then insert the code in blue. I then changed the name of the step to List of Columns.

"Merged Columns" = Table.CombineColumns(#"Changed Type",#"List of Columns",Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")

Also needs to be entered by hand.

Delete

Next convert to list.  Then we have already done the steps for the other table today.

To test simply go back to the original table TestList and add a row. Once you refresh you will see it in the list and the other table.

Alright! that was fun, eh?

Nathaniel

let
Source = TestList,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Call Center"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"List of Columns" = Table.ColumnNames(#"Changed Type"),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",#"List of Columns",Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
Merged = #"Merged Columns"[Merged]
in
Merged