Forum Discussion
Use 2 columns as 3
Hi all,
I'm trying to create something so I can use dynamic RLS, and I would like the best, optimized way to do it.
I have two columns :
DIMENSION1 DIMENSION2
RegionA FurnitureA
RegionA FurnitureB
And I would like to create this column, since I need the column to match with another column that will be created from 3 columns :
UNIFIED_DIMENSION
RegionA_FurnitureA_FurnitureB
RegionA_FurnitureB_FurnitureA
What is the best way to do it without altering report performance ?
Best regards,
Martin.
Hi Anonymous ,
(1) This is my test data.
(2) Create a new table, click “Advanced Editor” and copy and paste the following code.
let Table1 = Table1, Table2 = Table2, // Extend Table2 to include the combined DIMENSION2 value ExpandedTable2 = Table.AddColumn(Table2, "Combined_DIMENSION2", each [#"DIMENSION2-1"] & "_" & [#"DIMENSION2-2"]), // Creating the Cartesian product of Table1 for concatenation CartesianJoin = Table.AddColumn(Table1, "Temp", each ExpandedTable2), ExpandedCartesianJoin = Table.ExpandTableColumn(CartesianJoin, "Temp", {"Combined_DIMENSION2"}), // Filter rows to retain only the combined values of DIMENSION2 for rows containing DIMENSION2 in Table1 FilteredTable = Table.SelectRows(ExpandedCartesianJoin, each Text.Contains([Combined_DIMENSION2], [DIMENSION2])), // Creating UNIFIED_DIMENSION columns UnifiedDimensionTable = Table.AddColumn(FilteredTable, "UNIFIED_DIMENSION", each [DIMENSION1] & "_" & Text.BeforeDelimiter([Combined_DIMENSION2], "_") & "_" & Text.AfterDelimiter([Combined_DIMENSION2], "_")), // Select the desired columns and remove the redundant columns Result = Table.SelectColumns(UnifiedDimensionTable, {"EMAIL", "UNIFIED_DIMENSION"}), #"Removed Duplicates" = Table.Distinct(Result) in #"Removed Duplicates"Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous ,
You can create a calculated column.
Column = VAR _a = SELECTCOLUMNS ( FILTER ( 'Table', [DIMENSION1] = EARLIER ( 'Table'[DIMENSION1] ) ), "d2", [DIMENSION2] ) VAR _b = SELECTCOLUMNS ( FILTER ( 'Table', [DIMENSION1] = EARLIER ( 'Table'[DIMENSION1] ) && [DIMENSION2] = EARLIER ( 'Table'[DIMENSION2] ) ), "d2", [DIMENSION2] ) RETURN [DIMENSION1] & "_" & [DIMENSION2] & "_" & EXCEPT ( _a, _b )If I have misunderstood your needs please clarify in a follow up reply.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thank you for your swift reply !
While it helps, it doesn't exactly resolve the issue.
It can indeed create a new column, but it doesn't create exactly what I need, and it doesn't create any new rows.
My bad for not giving enough details, here is the more detailed explanation :
I'm starting from this :
EMAIL DIMENSION1 DIMENSION2 People1 RegionA FurnitureA People1 RegionA FurnitureB People2 RegionB FurnitureC What I need to get is this :
EMAIL UNIFIED_DIMENSION People1 RegionA_FurnitureA_FurnitureA People1 RegionA_FurnitureA_FurnitureB People1 RegionA_FurnitureA_FurnitureC People1 RegionA_FurnitureB_FurnitureA People1 RegionA_FurnitureC_FurnitureA People1 RegionA_FurnitureB_FurnitureB People1 RegionA_FurnitureB_FurnitureC People1 RegionA_FurnitureC_FurnitureB People2 RegionB_FurnitureC_FurnitureA People2 RegionB_FurnitureC_FurnitureB People2 RegionB_FurnitureC_FurnitureC People2 RegionB_FurnitureA_FurnitureC People2 RegionB_FurnitureB_FurnitureC The overall idea is that DIMENSION2 is divided into two columns in the target table (since the goal is to join two tables, one with a single DIMENSION2 column, and the other with two DIMENSION2 columns), and if in the first table a People has access to one value of DIMENSION2, then he must have access to every combination of DIMENSION2 values that includes at least once this value. DIMENSION1 is not divided.
Is there a way to do this in Power Query ?
Best regards,
Martin.
Hi Anonymous ,
(1) This is my test data.
(2) Create a new table, click “Advanced Editor” and copy and paste the following code.
let Table1 = Table1, Table2 = Table2, // Extend Table2 to include the combined DIMENSION2 value ExpandedTable2 = Table.AddColumn(Table2, "Combined_DIMENSION2", each [#"DIMENSION2-1"] & "_" & [#"DIMENSION2-2"]), // Creating the Cartesian product of Table1 for concatenation CartesianJoin = Table.AddColumn(Table1, "Temp", each ExpandedTable2), ExpandedCartesianJoin = Table.ExpandTableColumn(CartesianJoin, "Temp", {"Combined_DIMENSION2"}), // Filter rows to retain only the combined values of DIMENSION2 for rows containing DIMENSION2 in Table1 FilteredTable = Table.SelectRows(ExpandedCartesianJoin, each Text.Contains([Combined_DIMENSION2], [DIMENSION2])), // Creating UNIFIED_DIMENSION columns UnifiedDimensionTable = Table.AddColumn(FilteredTable, "UNIFIED_DIMENSION", each [DIMENSION1] & "_" & Text.BeforeDelimiter([Combined_DIMENSION2], "_") & "_" & Text.AfterDelimiter([Combined_DIMENSION2], "_")), // Select the desired columns and remove the redundant columns Result = Table.SelectColumns(UnifiedDimensionTable, {"EMAIL", "UNIFIED_DIMENSION"}), #"Removed Duplicates" = Table.Distinct(Result) in #"Removed Duplicates"Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.