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.
Hello,
I have the next table with null values, how can I move the values to only one row without affecting the data?
Table to fix:
What I need to get as a result:
Thank you.
Solved! Go to Solution.
let
Source = your_table,
func = (tbl) =>
[cols = Table.ToColumns(tbl),
to_table = Table.FromColumns(
List.FirstN(cols, 3) &
List.Transform(
List.RemoveFirstN(cols, 3),
(x) => List.Skip(x, (w) => w is null)
),
Table.ColumnNames(tbl)
)][to_table],
group = Table.Group(Source, {"Day", "Date", "Operator"}, {{"x", func}}),
result = Table.Combine(group[x])
in
result
Hi @Andresgamez28, similar approach here:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3sNA3MjAyUdJRck4sygFSxmZmRuZAGopidbCqAiIzKwMDIFJw9CWoFEl1gC9BpUBkaKhnClZkhN2BFqgOxKIKlwOxK8XqQJxK4Q6MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operator = _t, #"Docket No" = _t, #"Start Time" = _t, #"Finish Time" = _t, Hours = _t]),
// You can delete this step when applying on real data.
RepalceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
GroupedRows = Table.Group(RepalceBlankToNull, {"Date", "Operator"}, {{"All", each Table.FromColumns(List.Transform(Table.ToColumns(_), each List.FirstN(List.RemoveNulls(_), 1)), Value.Type(_)), type table}}),
Combined = Table.Combine(GroupedRows[All])
in
Combined
You can use this formula (Source is your data )
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
List={"Day", "Date", "Operator"},
Unpivoted = Table.UnpivotOtherColumns(Source, List, "Attribute", "Value"),
Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value", List.First),
Custom1 = Table.Sort( Pivoted & Table.Repeat(Table.SelectColumns(Pivoted,List),4), "Date")
in
Custom1
You can use this formula (Source is your data )
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
List={"Day", "Date", "Operator"},
Unpivoted = Table.UnpivotOtherColumns(Source, List, "Attribute", "Value"),
Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value", List.First),
Custom1 = Table.Sort( Pivoted & Table.Repeat(Table.SelectColumns(Pivoted,List),4), "Date")
in
Custom1
Hi @Andresgamez28, similar approach here:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3sNA3MjAyUdJRck4sygFSxmZmRuZAGopidbCqAiIzKwMDIFJw9CWoFEl1gC9BpUBkaKhnClZkhN2BFqgOxKIKlwOxK8XqQJxK4Q6MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operator = _t, #"Docket No" = _t, #"Start Time" = _t, #"Finish Time" = _t, Hours = _t]),
// You can delete this step when applying on real data.
RepalceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
GroupedRows = Table.Group(RepalceBlankToNull, {"Date", "Operator"}, {{"All", each Table.FromColumns(List.Transform(Table.ToColumns(_), each List.FirstN(List.RemoveNulls(_), 1)), Value.Type(_)), type table}}),
Combined = Table.Combine(GroupedRows[All])
in
Combined
amazing
let
Source = your_table,
func = (tbl) =>
[cols = Table.ToColumns(tbl),
to_table = Table.FromColumns(
List.FirstN(cols, 3) &
List.Transform(
List.RemoveFirstN(cols, 3),
(x) => List.Skip(x, (w) => w is null)
),
Table.ColumnNames(tbl)
)][to_table],
group = Table.Group(Source, {"Day", "Date", "Operator"}, {{"x", func}}),
result = Table.Combine(group[x])
in
result
What should happen when you have more than or less than one value per column ?