cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Moving and rearranging a column header and row values

Hi,

 

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

{7AB1262C-D397-4B7B-B3B5-3A0036CBF662}.png

Into something like this:

{E9FF8B75-6C3F-4573-816E-6945A5931960}.png

 

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.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

6 REPLIES 6
Jimmy801
Community Champion
Community Champion

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
Community Champion
Community Champion

Hi @Anonymous 

 

Sure, can you provide a sample

 

Thanks

Mariusz

Anonymous
Not applicable

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
Community Champion
Community Champion

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

View solution in original post

Anonymous
Not applicable

Hi!

Thank you!

 

It worked!

Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors