Forum Discussion
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 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?
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"
8 Replies
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.
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.
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)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=sharingThanks,
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.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.
