Forum Discussion
Sorting out table
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"
It looks like you've only specified your expectations. You'd need to provide some samples of raw data that represent the patterns of all data.
MasonMA vojtechsima Here is sample file.
File is unfiltered in original form only sensitive or personal information changed.
https://we.tl/t-8gcuJJtgZWAs you will see that is not the only mess in this file apart from it there is day's/ date each on its own column instead in one row under column.
But I have some solution for that problem even though it is a bit complicated one.
