Forum Discussion
Possible to copy a large table changing only one element?
I am creating a pretty large report that pulls similar data from multiple sources. I have worked out a table that has dozens of coumns, each with a lot of DAX of varying sophistication. I need to create a few dozen more tables that are identical but one set changes one particular (recurring) variable in the DAX (i.e. AAA replaced with BBB, while leaving everythin else unchanged), and another set changes a different variable, etc.
Is this possible? There is so much DAX I developed but doesn't change (outside the one variable in each) that even copying and pasting one column at a time takes forever. The ability to export a table, search and replace All on one element, then re-import it as a different table would be great.
Is there a way to do this using Excel or similar?
HI Anonymous,
I also think union functions with selectcolumns show suitable for your scenarios.
You can use them to selectcolumns to pick up the table and change some of the value of their fields or add expression to calculate, the union function will merge them as a new table. You can write DAX expression on it instead of duplicate these formulas on each table.New Table = UNION ( SELECTCOLUMNS ( Table1, "ID", [ID], "Date", [Create], "Price", [Price], "Amount", [Sales] ), SELECTCOLUMNS ( Table2, "ID", [ID], "Date", [DateIn], "Price", [Price], "Amount", [Qty] ), SELECTCOLUMNS ( Table3, "ID", [ID], "Date", [Finish], "Price", [Price], "Amount", [Value] ) )Reference links of Dax functions:
BTW, you can also add filters nested into selectcolumns table expressions to filter records if there existed some of the records you not want them to show into the merged new table.
Regards,
Xiaoxin Sheng
3 Replies
- amitchandak
Super User
Anonymous , union with summarize and SELECTCOLUMNS , there you can change the value , In summarize keep that column at last
Table = union( SELECTCOLUMNS(Instagram, "Index",[Index], "row",[Row]) ,SELECTCOLUMNS(Instagram, "Index",[Index], "row",1) )also refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
I am not entirely sure I am understanding your suggestion, but it could just be my limited Power BI experience.
Let's say for example I have a table with one column, and the column has the DAX shown below, but I want to replace every instance of REV (irrespective of where it falls in the expression) with RFV.
What would the UNION look like?
G1 =CALCULATE(SUM('Causals'[Quantity]),FILTER('Causals', 'Causals'[Test Month] = 'Level 1 REV'[Month] && 'Causals'[Test Year] = 'Level 1 REV'[Calendar Year] && 'Causals'[Site] = "REV" && 'Causals'[Causal Lvl 1] = "G1"))HI Anonymous,
I also think union functions with selectcolumns show suitable for your scenarios.
You can use them to selectcolumns to pick up the table and change some of the value of their fields or add expression to calculate, the union function will merge them as a new table. You can write DAX expression on it instead of duplicate these formulas on each table.New Table = UNION ( SELECTCOLUMNS ( Table1, "ID", [ID], "Date", [Create], "Price", [Price], "Amount", [Sales] ), SELECTCOLUMNS ( Table2, "ID", [ID], "Date", [DateIn], "Price", [Price], "Amount", [Qty] ), SELECTCOLUMNS ( Table3, "ID", [ID], "Date", [Finish], "Price", [Price], "Amount", [Value] ) )Reference links of Dax functions:
BTW, you can also add filters nested into selectcolumns table expressions to filter records if there existed some of the records you not want them to show into the merged new table.
Regards,
Xiaoxin Sheng
