Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Moving and rearranging a column header and row values

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.

Need Help
Tips and Tricks
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Sure, can you provide a sample

     

    Thanks

    Mariusz

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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