Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
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 ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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"
vojtechsima's avatar
vojtechsima
Super User
1 year ago

Hello, Justas4478 ,

from this:

 


I guess you want this:


See following code:

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"NAME", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "shift", each if Text.Contains([NAME], "(") then [NAME] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"shift"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([NAME], "("))
in
    #"Filtered Rows"


Steps:
Promote Headers,
Create new column with shift, if text contains "(), add the value from Name column or keep it blank
Fill down the Shift Column
FIlter out shift values (I did again, if it has (, remove it)