Moving and rearranging a column header and row values

[Anonymous]

Hi,

Could you suggest a solution in PowerQuery to transform a table like this:

Into something like this:

As you see - agent name comes above the rest of the data and I would like it to be in every row to be able to create a report that shows how long each person spends in a primary and secondary statuses.

Thank you.

[Jimmy801]

Hello @Anonymous ,

this depends on the stability of your data.

If for the name-information, Column 4 is always empfty, you can apply the following logic (It's not the same database as you provided.. but to show the logic)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUYKiWJ1opeCSxKIShZDM3FSgiGteCowZUJSZm1hUqQCULyktBgoEpybn56UgCYF0GxrqGxnoG1oqABWA2KYgtoKjL5Drn5aWk5mXisSCaTDFrsGxLDEzJzEpB6TFychJIbGgICdTjxRdCDZNrHIqSk3MhtPoinF62TElMVchKD89tah4KAQ90YHon6cQWJpamorMHFL2YA+pWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colum1 = _t, Colum2 = _t, Colum3 = _t, Colum4 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Colum1", type text}, {"Colum2", type text}, {"Colum3", type text}, {"Colum4", type text}}),
    AddedCustom = Table.AddColumn
    (
        ChangedType,
        "Name", 
        each if [Colum3]="" then [Colum1] else null
    ),
    FilledDown = Table.FillDown
    (
        AddedCustom,
        {"Name"}
    ),
    FilteredRows = Table.SelectRows
    (
        FilledDown, 
        each ([Colum3] <> "")
    ),
    PromotedHeaders = Table.PromoteHeaders
    (
        FilteredRows, 
        [PromoteAllScalars=true]
    ),
    ChangedType1 = Table.TransformColumnTypes
    (
        PromotedHeaders,
        {{"Start Time", type text}, {"End Time", type text}, {"Primary Status", type text}, {"Secondary Status", type text}, {"John Smith", type text}}),
    FilteredRows1 = Table.SelectRows
    (
        ChangedType1, 
        each ([Start Time] <> "Start Time")
    )
in
    FilteredRows1

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Mariusz]

Hi @Anonymous 

Sure, can you provide a sample

Thanks

Mariusz

[Anonymous]

Hi,

Here is a sample of the data:

Link to download

It comes in the same format every day where I need to accumulate the durations on each name and show the split for every date.

Thanks.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

tried to apply my logic to your database.

It works just.

Here the code I used

let
    Quelle = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [Column4]= null then [Column1] else null),
    #"Nach unten gefüllt" = Table.FillDown(#"Hinzugefügte benutzerdefinierte Spalte",{"Benutzerdefiniert"}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Nach unten gefüllt", each ([Column4] <> null)),
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Gefilterte Zeilen", [PromoteAllScalars=true]),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Start Time", type text}, {"Column2", type any}, {"End Time", type text}, {"Primary Status", type text}, {"Column5", type any}, {"Secondary Status", type text}, {"Duration", type text}, {"John Smith", type text}}),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Geänderter Typ1", each ([Start Time] <> "Start Time"))
in
    #"Gefilterte Zeilen1"

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Hi!

Thank you!

It worked!

[Jimmy801]

Hello @Anonymous 

so you think that the logic solving the issue in my above post should work?

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy