Pivot or Unpivot Multiple Columns

Hi,

Here's a better solution.  Even if the number of Data/Value combinations per client increase/decrease, the formatting would get applied automatically.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ClientID"}, "Attribute", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"ClientID", type text}}, "en-IN"),{"ClientID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value.1", "Index"}, {"Value.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Merged", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ClientID", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ClientID", Int64.Type}, {"Merged.2", type text}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1"),
    datecolumnnames = Table.ColumnNames(#"Pivoted Column"),
    #"Converted to Table" = Table.FromList(datecolumnnames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]="ClientID" then "text" else if Text.Start([Column1],4)="Date" then "date" else "number"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.Type(if [Custom]="date" then #date(2023,1,1) else if [Custom]="number" then 1 else "A")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    Custom1 = List.Zip({#"Removed Columns"[Column1],#"Removed Columns"[Custom.1]}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",Custom1)
in
    #"Changed Type2"

Thank you! Both of your solutions worked very well and was easy for me to incorporate. For my own learning and understanding what is the Partition step and what did you use for that step? I've never used Partition and I am not finding it in the ribbon options. Also, how did you create the datecolumnnames? Was that custom code or a ribbon option?

Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

datecolumnnames = Table.ColumnNames(#"Pivoted Column"),