Forum Discussion
RLS and column values
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!
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.
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
10 Replies
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.
NathanielNathaniel_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!
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 ,
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.
NathanielAnonymous ,
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.
NathanielSo 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