Sorting out table

[Justas4478]

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 and to populate emplyees that are in the specified shift.
Above is expected outcome.
Any one know of a way I can archieve this result?

[v-pnaroju-msft]

Thank you, @vojtechsima , @danextian , and @ajaybabuinturi, for your responses.

Hi Justas4478,

We would like to check if the solution provided by @vojtechsima, @danextian, @ajaybabuinturi has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
If you found the response helpful, please mark the respective reply that resolved the issue as the accepted solution. This recognition benefits other members seeking solutions to similar queries.

Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[ajaybabuinturi]

Hi @Justas4478,

You need to add custom column to get only Employee Names: Click on Add Column tab>> Cutsom Column >> Add below formula

= if not Text.Contains([#"NAME "], "Warehouse Operative") then [#"NAME "] else null)

Then select the shift column >> Transform tab >> Click on Fill as UP

After all these steps remove the NULL values.

Results:

Here the .pbix file: https://drive.google.com/file/d/1vOju4jmEZUF1dfUvU_FK0o924DP08w2v/view?usp=sharing 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[danextian · Accepted solution]

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"

[Justas4478]

I used @danextian provided solution since it seams simple enought where it wouldnt cause any potential problems.

[vojtechsima]

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)

[MasonMA]

@Justas4478 

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. 

[Justas4478]

@MasonMA @vojtechsima Here is sample file.
File is unfiltered in original form only sensitive or personal information changed.
https://we.tl/t-8gcuJJtgZW

[Justas4478]

As 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.