Transform Column1 to ColumnHeaders

Hi Anonymous 

I have taken only column1 and column 2 in my excel file:


Here is the M query:
let
Source = Excel.Workbook(File.Contents("File_Name.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Group", each if [Column1] = "Full Name" then [Column2] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Group"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column1]), "Column1", "Column2"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
#"Removed Columns"

This should give you the desired result:

hi powerbidev123 

Here is a simple 5 mins solution to your problem in 2 different ways

How to fix the "Too many elements in the enumeration to complete..." pivot error in Power Query

Was able to get the expected output here

Ritesh

✔️Please mark the answer if helpful so that it can help others as well🙏

Hi Anonymous 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZDBasMwDIZfRfgcWFJa2DW066Frt7HmFnLQMoWGObKxlYHffo5hpKPtDmPgg9H3+xef61ptR63hCQdSmSrfMCBncED3oZqsVlUvegLPlhxKb9hD6b0k9jBYbcJALDGwNiwOWxlRJ7g3yPBKmtATbFCmkqK4W8STL5ZzpBzMmAqWeZ7P4yrY6cURNboA02hmL6Ozxiccd4MYsOjizafIuc4GW2QMGeyo6xyFH0qPvbQnYjgKdt2lUOqp+tjzm87qlk5xTWeN/gTl+ydyS9d8Lvi5zFYbRz66fH/Gv4rc3xJZ/cGD40rVNF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "GroupMarker", each if [Column1] =  "Full Name" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupMarker"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"GroupMarker"}, {{"Grouped", each _, type table [Column1=nullable text, Column2=nullable text, GroupMarker=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Pivoted", each Table.Pivot([Grouped], List.Distinct([Grouped][Column1]), "Column1", "Column2")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"GroupMarker", "Grouped"}),
    #"Expanded Pivoted" = Table.ExpandTableColumn(#"Removed Columns1", "Pivoted", {"Full Name", "Title", "Employment", "Loan Release Date", "Loan Amount", "Loan Type", "Loan Purpose"}, {"Full Name", "Title", "Employment", "Loan Release Date", "Loan Amount", "Loan Type", "Loan Purpose"})
in
    #"Expanded Pivoted"