Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Is it there any way so that I can append a column under another, however keeping information of further columns? What I aim to do is something akin to the following example (here is a .pbix file I've created in order to illustrate my point).
So I was able to create it in Power Query, however my original file has more than three hundred thousand rows and a lot of columns, so if it is possible to create in DAX it would definitely help me a lot because in Power Query it takes a way too longer than it certainly takes in DAX.
Thanks in advance.
Solved! Go to Solution.
Hi @Anonymous
You can use
Appended =
UNION (
SELECTCOLUMNS ( Main_tbl, "Salesperson", Main_tbl[Salesperson], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] ),
SELECTCOLUMNS ( Main_tbl, "Salesperson2", Main_tbl[Salesperson2], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] )
)
@Anonymous
Even more simple and dynamic solution
Appended2 =
UNION (
ALLEXCEPT ( Main_tbl, Main_tbl[Salesperson2] ),
ALLEXCEPT ( Main_tbl, Main_tbl[Salesperson] )
)
Go for DAX. It is much more faster and much easier.
Interesting idea; but the only thing worth mentioning is that ALLEXCEPT(), when used as table function, returns a summarized table, like what SUMMARIZE() does.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Easy task in PQ and flexible regardless of column names.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJK0pNUdJR8kjNK6oE0qYGQMLAVN/ISN/IwMgIyHHLzEvMS04tVorViVZySi3KSyxC1mBhiq4huKQosSQ1PTMZrMM7sSg1D5sFBjD1volF2aklmXnpYPUBqSWpRUjqLQ0h6g0tsZsPUxeSkZ+bWKzgqwdykzlUjwV2OxC+QNZlZoauC8XrMI+gaMGwCEULzC8objPBE16xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Salesperson = _t, Salesperson2 = _t, Target = _t, Date = _t, Area = _t]),
Reshaped = let cols = Table.ToColumns(Source), names = List.RemoveRange(Table.ColumnNames(Source),1) in Table.FromColumns(List.Skip(cols),names) & Table.FromColumns(List.RemoveRange(cols,1),names)
in
Reshaped
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks, man. I couldn't find a way to make it work using DAX (now @tamerj1 showed a way), and if I did it using PQ it would properly work.
Hi @Anonymous
You can use
Appended =
UNION (
SELECTCOLUMNS ( Main_tbl, "Salesperson", Main_tbl[Salesperson], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] ),
SELECTCOLUMNS ( Main_tbl, "Salesperson2", Main_tbl[Salesperson2], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |