Forum Discussion
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: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"
Hi powerbidev123 -- here's the reformatted tables. Hopefully that helps.
ORIGINAL TABLE
| Name | Column1 | Column2 |
| employee_financial_assistance_markabayan_20241121.xlsx | Full Name | Abayan, Mark |
| employee_financial_assistance_markabayan_20241121.xlsx | Title | Operations Asst |
| employee_financial_assistance_markabayan_20241121.xlsx | Employment | Contractual |
| employee_financial_assistance_markabayan_20241121.xlsx | Loan Release Date | 11/21/2024 |
| employee_financial_assistance_markabayan_20241121.xlsx | Loan Amount | 4000 |
| employee_financial_assistance_markabayan_20241121.xlsx | Loan Type | Salary Loan |
| employee_financial_assistance_markabayan_20241121.xlsx | Loan Purpose | Sent to parents |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Full Name | Dacanay, Jeffrey |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Title | Kitchen Staff |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Employment | Full Time |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Loan Release Date | 11/25/2024 |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Loan Amount | 1000 |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Loan Type | Cash Advance |
| employee_financial_assistance_jeffreydacanay_20241125.xlsx | Loan Purpose | Cash Advance |
| employee_financial_assistance_joanflores_20241128.xlsx | Full Name | Flores, Joan |
| employee_financial_assistance_joanflores_20241128.xlsx | Title | Kitchen Staff |
| employee_financial_assistance_joanflores_20241128.xlsx | Employment | Full Time |
| employee_financial_assistance_joanflores_20241128.xlsx | Loan Release Date | 11/28/2024 |
| employee_financial_assistance_joanflores_20241128.xlsx | Loan Amount | 500 |
| employee_financial_assistance_joanflores_20241128.xlsx | Loan Type | Cash Advance |
| employee_financial_assistance_joanflores_20241128.xlsx | Loan Purpose | null |
TARGET TABLE
| Full Name | Title | Employment | Loan Release Date | Loan Amount | Loan Type | Loan Purpose |
| Abayan, Mark | Operations Asst | Contractual | 11/21/2024 | 4000 | Salary Loan | Sent to parents |
| Dacanay, Jeffrey | Kitchen Staff | Full Time | 11/25/2024 | 1000 | Cash Advance | Cash Advance |
| Flores, Joan | Kitchen Staff | Full Time | 11/28/2024 | 500 | Cash Advance | null |
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),
Header = List.Distinct(#"Removed Columns"[Column1]),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Record.ToList(_)),
Custom1 = Table.Combine(List.Transform(List.Split(#"Added Custom"[Custom],7), each Table.FromColumns(_,Header))),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Full Name] <> "Full Name")),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Filtered Rows", {{"Loan Release Date", type date}}, "en-US"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Full Name", type text}, {"Title", type text}, {"Employment", type text}, {"Loan Release Date", type date}, {"Loan Amount", Int64.Type}, {"Loan Type", type text}, {"Loan Purpose", type text}})
in
#"Changed Type"