Forum Discussion
Solved
Sorting out table
Hi, I have messy table from agency. There are many things needs to be sorted. One of them is that shift is added in to same column as employee name. I am trying to move shifts to new column ...
Hi Justas4478
In the query editor, add a custom column that checks whether [NAME] contains both "(" and ")" and return the value of [NAME] if true else null. Call this Shift. Fill down this column. Exclude rows where [NAME] = [Shift].
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8sSs3ILy1OVfAvSC1KLMksS1XQMEvM1TUqyNVUitWJVvLKz8hTcMlPBXN8E4sqFbwS84C8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t]), #"Added Custom" = Table.AddColumn(Source, "Shift", each if Text.Contains([NAME], "(") and Text.Contains([NAME], ")") then [NAME] else null, type text), #"Filled Down" = Table.FillDown(#"Added Custom",{"Shift"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [NAME] <> [Shift]) in #"Filtered Rows"
Hi Justas4478
In the query editor, add a custom column that checks whether [NAME] contains both "(" and ")" and return the value of [NAME] if true else null. Call this Shift. Fill down this column. Exclude rows where [NAME] = [Shift].
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8sSs3ILy1OVfAvSC1KLMksS1XQMEvM1TUqyNVUitWJVvLKz8hTcMlPBXN8E4sqFbwS84C8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t]),
#"Added Custom" = Table.AddColumn(Source, "Shift", each if Text.Contains([NAME], "(") and Text.Contains([NAME], ")") then [NAME] else null, type text),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Shift"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [NAME] <> [Shift])
in
#"Filtered Rows"
I used danextian provided solution since it seams simple enought where it wouldnt cause any potential problems.