Forum Discussion
Transpose the table with specific rule
Thanks for your prompt response
LK0913 ,
I have modified the M code and uploaded the PBIX file here. Could you please review it and let us know your feedback?
We truly appreciate your continued engagement and thank you for being an active and valued member of the community.
Best Regards,
Lakshmi.
Hi LK0913, another solution:
Outputlet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bclLCsAgDEXRvWQs1MTE1KGfXYj730aTQimCb/AG98wJtVYIQET2GK94XxRJYIWPEPH9A6WUnGij1ppFZnZKBxIRJ96o924x29xkszGGxVKKUz6QqjrpT+sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SHO = _t, SO = _t, #"SO Date" = _t]), ColNames = List.Buffer(List.Skip(Table.ColumnNames(Source))), Transformed = Table.Combine(Table.Group(Source, {"SHO"}, {{"T", each [ a = List.Alternate(List.Combine(Table.ToRows(_)), 1, List.Count(ColNames), 0), b = List.Accumulate({1..(List.Count(a) / List.Count(ColNames))}, {}, (st,cur)=> st & List.Transform(ColNames, (x)=> let h1 = Text.Split(x, " ") in h1{0} & Text.From(cur) & " " & Text.Combine(List.Skip(h1), " ") )) , c = Table.FromRows({ {[SHO]{0}} & a }, {"SHO"} & b) ][c], type table}}, 0)[T]) in TransformedYes, it works in the way i expected and i wanted, thank you for your effort. I am wondering it writting a M code is only way to reach this or perharps there is someething that i can do myself step by step ? Thanks 🙂
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
group = Table.Group(
Source,
"SHO",
{"rows", (x) => {Table.FirstValue(x)} & List.Combine(Table.ToList(Table.Sort(x, "SO Date"), List.Skip))}
),
result = Table.FromList(
group[rows],
(x) => x,
{"SHO"} &
List.Combine(
List.Transform(
List.Transform({1..(List.Max(List.Transform(group[rows], List.Count)) - 1) / 2}, Text.From),
(x) => {"SO" & x, "SO" & x & " Date"}
)
)
)
in
resultHey AlienSx,
This is exaclty what i need to get as final resulut, but in PowerBI, not excel, and this works, but its confusing to me, i cant make it myself, i mean i will not be abe to replicate it. Thanks
Thanks for your prompt response
LK0913 ,
I have modified the M code and uploaded the PBIX file here. Could you please review it and let us know your feedback?
We truly appreciate your continued engagement and thank you for being an active and valued member of the community.
Best Regards,
Lakshmi.
Yes, it works in the way i expected and i wanted, thank you for your effort. I am wondering it writting a M code is only way to reach this or perharps there is someething that i can do myself step by step ? Thanks 🙂