Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I've the following table:
data Numbers
id | 40 |
number | 3434 |
year | 2007 |
rank | 23 |
school | 1 |
uni 1 | 14 |
uni 2 | 10 |
id | 343 |
number | 78778 |
year | 2007 |
rank | 87 |
school | 54 |
uni 1 | 2 |
uni 2 | 30 |
I would like the following result but am unsure how to get there. I tried a transpose but this moves all rows as columns. My desired result is as follows:
Thanks for any help.
Solved! Go to Solution.
@jimmyfromus , @StefanoGrimaldi , as long as each group of records starts with ID like this, this solution applies; order and presence of other fields (number, year, rank ...) make no difference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEyNDRUitWJVsorzU1KLQIKGJsYm4BFKlMTQXwjAwNzML8oMS8bxDcG84qTM/Lzc0AGgLmleZkKhiCeCZxrBOIagLlgm4yMjPCZa2GOaq4pqknGBqjONLcwN7dAsdoIYZWxsTFNvGBigj1oSHJkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
Rearrange = Table.Combine(List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
Rearrange
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! |
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc07CoAwEEXRrcjUFvmMTPYSUvgDgxpBSeHuzYwgpLE8r3jXe4gTtIAKQush5X2Yz2KLFmW5555tlCLx2aeVbUXXuBzHVqyFOcVGs/CjYb7nEirHdYkckftLOapTHVYtU6VsSYUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}, {"Numbers", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
Partition = Table.Group(#"Added Index", {"data"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Numbers", "Index", "Index1"}, {"Numbers", "Index", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[data]), "data", "Numbers"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"id", Int64.Type}, {"number", Int64.Type}, {"year", Int64.Type}, {"rank", Int64.Type}, {"school", Int64.Type}, {"uni 1", Int64.Type}, {"uni 2", Int64.Type}})
in
#"Changed Type1"
Hope this helps.
@jimmyfromus if the source is csv or text file I will use the text by example and it will work just fine. read more here.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi, @jimmyfromus , you might want to try the following codes,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc07CoAwEEXRrcjUFvmMTPYSUvgDgxpBSeHuzYwgpLE8r3jXe4gTtIAKQush5X2Yz2KLFmW5555tlCLx2aeVbUXXuBzHVqyFOcVGs/CjYb7nEirHdYkckftLOapTHVYtU6VsSYUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
#"All Columns" = Table.ToColumns(Source),
#"New Table" = Table.PromoteHeaders(Table.Transpose(Table.FromColumns({List.Skip(#"All Columns"{0},7)} & List.Split(#"All Columns"{1}, 7))), [PromoteAllScalars=true])
in
#"New Table"
or
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEyMVCK1YlWyivNTUotAvKNTYxNwCKVqYkgvpGBgTmYX5SYlw3iG4N5xckZ+fk5QL4hmFual6lgCOKZwLlGIC7EcLBFQIPxmWthjmquKapJxmjONLcwN7dAsdpIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
Custom1 = Table.Combine(List.Transform(Table.Split(Source,7), each Table.PromoteHeaders(Table.Transpose(_),[PromoteAllScalars=true])))
in
Custom1
here's another solution, which is only a matter of several clicks on UI,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc07CoAwEEXRrcjUFvmMTPYSUvgDgxpBSeHuzYwgpLE8r3jXe4gTtIAKQush5X2Yz2KLFmW5555tlCLx2aeVbUXXuBzHVqyFOcVGs/CjYb7nEirHdYkckftLOapTHVYtU6VsSYUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US")[Index]), "Index", "Numbers"),
#"Transposed Table" = Table.Transpose(#"Pivoted Column"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
All solutions achieve the same result.
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! |
@CNENFRNL , @StefanoGrimaldi Thanks, As Stefano mentioned the data, as I've just noticed, is not all in chronolgical order. ID for example is in row 1,8,15 but then 21. What can I do about that? Thanks.
@jimmyfromus , @StefanoGrimaldi , as long as each group of records starts with ID like this, this solution applies; order and presence of other fields (number, year, rank ...) make no difference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEyNDRUitWJVsorzU1KLQIKGJsYm4BFKlMTQXwjAwNzML8oMS8bxDcG84qTM/Lzc0AGgLmleZkKhiCeCZxrBOIagLlgm4yMjPCZa2GOaq4pqknGBqjONLcwN7dAsdoIYZWxsTFNvGBigj1oSHJkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
Rearrange = Table.Combine(List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
Rearrange
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! |
Hi,
I have a question, I copy-paste the
Rearrange = Table.Combine(List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_)))) in Rearrange
and I changed "data" into "F1" (my first column name). However, I received Token Comma Expected for Rearrange. Any ideas? Thanks
@CNENFRNL That's great, thanks. I'm just a little stuck on the final part. I renamed the column to data in the step before but I get the error: Expression.Error: The column 'data' of the table wasn't found.Details: data.
#"Transposed Table" = Table.Transpose(Source),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Transposed Table", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ":"), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Column1], ":"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "data"}}),
#"Rearrange" = Table.Combine(#"Renamed Columns",List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
#"Rearrange"
@jimmyfromus , what does your table look like until the step #"Renamed Columns"?
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! |
@jimmyfromus , all seems normal to me. Subsequent steps are supposed to work well.
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! |
@CNENFRNL I just had to change the last line slightly. Thanks again.
#"Transposed Table" = Table.Transpose(Source),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Transposed Table", "Data", each Text.BeforeDelimiter([Column1], ":"), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Numbers", each Text.AfterDelimiter([Column1], ":"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}),
#"Rearrange" = Table.Combine(List.Transform(Table.Group(#"Removed Columns", "Data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
#"Rearrange"
but that would work only if the list its in cronological order no? if the second Id was the one that dhould be as second row it wont, unless you assume its allways in cronological order as it was inputed the data?
Proud to be a Super User!
@StefanoGrimaldi , better to learn more and to comment less
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEyMVCK1YlWyivNTUotAvKNTYxNwCKVqYkgvpGBgTmYX5SYlw3iG4N5xckZ+fk5QL4hmFual6lgCOKZwLlGIC7EcLBFQIPxmWthjmquKapJxmjONLcwN7dAsdpIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
Custom1 = Table.Combine(List.Transform(Table.Split(Source,7), each Table.PromoteHeaders(Table.Transpose(_),[PromoteAllScalars=true])))
in
Custom1
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! |
I asked because I see your solution does work, but work under one scenario, I asked will it work in the scenario I put in? cause if the case if the data isnt stored in cronological order ifor each item on the file for example that code wont know how to assign to each row each value, or will it and how? would be learning more if you can answer that question 🙂 as you can see the guy that posted the topic its saying about that same topic of the cronological order of the data and isnt right.
Proud to be a Super User!
you have a problem there, cause you dont have a way to identify that id 40 its the first line for example and that the uni 1 value of 14 goes with id 40 for example.
if you had ID 1 = 40, number 1 = 3434 you would have a way to do so, but not the caso so you dont knoe what value goes to each row.
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |