Power Query Transpose Table

Question

Need help to achieve the desired output as shown below. I'm using Microsoft Excel 2013 with Power Query. Please help. Thanks!

Input Text (test.txt)

Menu1:1>1
Menu2:1>2
Menu3:1>3
Menu1:2>1
Menu2:2>2
Menu3:2>3
Menu1:3>1
Menu2:3>2
Menu3:3>3
Menu4:3>4
Menu1:4>1
Menu2:4>2
Menu3:4>3
Menu1:5>1
Menu2:5>2
Menu3:5>3
Menu1:6>1
Menu2:6>2
Menu3:6>3
Menu4:6>4

Power Query

let
    Source = Csv.Document(File.Contents("C:\test.txt"),[Delimiter=":", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Type"}, {"Column2", "Value"}}),
    GroupedRows = Table.Group(#"Renamed Columns", {"Type"}, {{"DistinctValues", each _[Value]}}),
    Output = Table.FromColumns(GroupedRows[DistinctValues], GroupedRows[Type])
in
    Output

Output

Menu1   Menu2   Menu3   Menu4
1>1     1>2     1>3     3>4
2>1     2>2     2>3     6>4
3>1     3>2     3>3 
4>1     4>2     4>3 
5>1     5>2     5>3 
6>1     6>2     6>3 

---

Desire Output

Menu1   Menu2   Menu3   Menu4
1>1     1>2     1>3     
2>1     2>2     2>3     
3>1     3>2     3>3     3>4
4>1     4>2     4>3 
5>1     5>2     5>3 
6>1     6>2     6>3     6>4

Answer 1

Solution from Technet by Ehren-MST. Accept any input value.

---

let
        Source = Csv.Document(File.Contents("C:\test.txt"),[Delimiter=":", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "Menu1" then [Index] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Index"}}),
        #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Column1]), "Column1", "Column2"),
        #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
     in
         #"Removed Columns1"

---

Change only this code for other data;

 #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "Menu1" then [Index] else null),

"Menu1" need to be change to suit your data. tq All..