Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi ,
I need help achieve this expected output where the resultant is a transposed data. Row scrore is transposed into a column but instead of manually renaming as Country1_Score is there an automated way and automatically create the sheet name as another column
Solved! Go to Solution.
Hi @han_rj, check this:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lFCoFidaCjTOb80r6SoUsEQVSY4tbg4Mz/PEME0QjCNEUwTsIbg5PyiVKCgoQGQMAURRmAWWNI3NSUxByQExMZgUaBCJBfgdJIRNZxkCrXZDKt7DKBuAronFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
// You can probably delete this step.
ReplacedBlankWithNull = Table.TransformColumns(Source,{}, each if _ = "" then null else _),
FilteredRows = Table.SelectRows(ReplacedBlankWithNull, each not List.IsEmpty(List.RemoveNulls(Record.ToList(_)))),
FnCheckCountry = each List.Contains(Record.ToList(_), "Country", (x,y)=> Text.StartsWith(x ?? "", y)),
RemovedTopRows = Table.Skip(FilteredRows, each not FnCheckCountry(_)),
Ad_GroupHelper = Table.AddColumn(RemovedTopRows, "GroupHelper", each FnCheckCountry(_), type logical),
GroupedRows = Table.Group(Ad_GroupHelper, "GroupHelper", {{"T", each
[ a = Table.RemoveColumns(_, {"GroupHelper"}),
b = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table.Skip(a)))),
c = List.Transform(Table.ColumnNames(b), (x)=> Text.Combine({List.RemoveNulls(Record.ToList(a{0})){0}, x}, "_")),
d = Table.RenameColumns(b, List.Zip({ Table.ColumnNames(b), c }))
][d], type table}}, 0,
(x,y)=> Byte.From(y = true) ),
T = GroupedRows[T],
Combined = Table.FromColumns(List.Combine(List.Transform(T, Table.ToColumns)), List.Combine(List.Transform(T, Table.ColumnNames)) ),
RenamedColumns = Table.RenameColumns(Combined,{{Table.ColumnNames(Combined){0}, "Type"}}),
RemovedOtherColumns = Table.SelectColumns(RenamedColumns, List.Select(Table.ColumnNames(RenamedColumns), (x)=> not Text.EndsWith(x, "Column1")))
in
RemovedOtherColumns
Hi @han_rj, check this:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lFCoFidaCjTOb80r6SoUsEQVSY4tbg4Mz/PEME0QjCNEUwTsIbg5PyiVKCgoQGQMAURRmAWWNI3NSUxByQExMZgUaBCJBfgdJIRNZxkCrXZDKt7DKBuAronFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
// You can probably delete this step.
ReplacedBlankWithNull = Table.TransformColumns(Source,{}, each if _ = "" then null else _),
FilteredRows = Table.SelectRows(ReplacedBlankWithNull, each not List.IsEmpty(List.RemoveNulls(Record.ToList(_)))),
FnCheckCountry = each List.Contains(Record.ToList(_), "Country", (x,y)=> Text.StartsWith(x ?? "", y)),
RemovedTopRows = Table.Skip(FilteredRows, each not FnCheckCountry(_)),
Ad_GroupHelper = Table.AddColumn(RemovedTopRows, "GroupHelper", each FnCheckCountry(_), type logical),
GroupedRows = Table.Group(Ad_GroupHelper, "GroupHelper", {{"T", each
[ a = Table.RemoveColumns(_, {"GroupHelper"}),
b = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table.Skip(a)))),
c = List.Transform(Table.ColumnNames(b), (x)=> Text.Combine({List.RemoveNulls(Record.ToList(a{0})){0}, x}, "_")),
d = Table.RenameColumns(b, List.Zip({ Table.ColumnNames(b), c }))
][d], type table}}, 0,
(x,y)=> Byte.From(y = true) ),
T = GroupedRows[T],
Combined = Table.FromColumns(List.Combine(List.Transform(T, Table.ToColumns)), List.Combine(List.Transform(T, Table.ColumnNames)) ),
RenamedColumns = Table.RenameColumns(Combined,{{Table.ColumnNames(Combined){0}, "Type"}}),
RemovedOtherColumns = Table.SelectColumns(RenamedColumns, List.Select(Table.ColumnNames(RenamedColumns), (x)=> not Text.EndsWith(x, "Column1")))
in
RemovedOtherColumns
The steps to get to your output will be a bit involved. Hope you can follow my descripiton here:
Start by adding a calculated column (let's call this Column 6) and setting it to Column 3 IF the contents of Column 1, 2, 4 or 5 are NULL.
Do a Fill Down to propagate that value to the rows below.
Assign column names manually: "Type", Session1, 2, 3, 4, and Country
Filter out where the first column (Type, or whatevery you name it) is null. You should now have a dataset of four rows and 6 columns.
Now experiment with various PIVOT and UNPIVOT operations.
Hope that helps
Proud to be a Super User! | |