Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi -- this feels like a very basic transformation issue but for the life of me I cannot figure out the proper steps of getting to my desired table. Any help is much appreciated!
ORIGINAL TABLE
Column1 Column2
Full Name Abayan, Mark
Loan Release Date 11/21/2024
Loan Amount 4000
Loan Type Salary Loan
Loan Purpose Sent to parents
Full Name Dacanay, Jeffrey
Loan Release Date 11/25/2024
Loan Amount 1000
Loan Type Cash Advance
Loan Purpose Cash Advance
Full Name Flores, Joan
Loan Release Date 11/28/2024
Loan Amount 500
Loan Type Cash Advance
Loan Purpose null
TARGET RESULT TABLE
Full Name Loan Release Date Loan Amount Loan Type Loan Purpose
Abayan, Mark 11/21/2024 4000 Salary Loan Sent to parents
Dacanay, Jeffrey 11/25/2024 1000 Cash Advance Cash Advance
Flores, Joan 11/28/2024 500 Cash Advance null
Cheers,
David
Solved! Go to Solution.
Hi @dyabes
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 @dyabes
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"
Proud to be a Super User!
Thank you everyone! Tried all suggestions and they all work. The gist is to have a helper column that will be used for GROUP BY function.
Hi @dyabes
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"
Proud to be a Super User!
Hi @dyabes
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,
I am not sure what is the source format of this data. For example, does it has two seperate columns or both column1& Column2 are part of one column?
So I have done the transformation assuming both column 1 and column 2 are two seperate columns and saved in excel.
Column1 | Column2 |
Full Name | Abayan, Mark |
Loan Release Date | 11/21/2024 |
Loan Amount | 4000 |
Loan Type | Salary Loan |
Loan Purpose | Sent to parents |
Full Name | Dacanay, Jeffrey |
Loan Release Date | 11/25/2024 |
Loan Amount | 1000 |
Loan Type | Cash Advance |
Loan Purpose | Cash Advance |
Full Name | Flores, Joan |
Loan Release Date | 11/28/2024 |
Loan Amount | 500 |
Loan Type | Cash Advance |
Loan Purpose | null |
The below are the transformation steps in Power Query:
Step1: Create a conditional column call it as 'Group' and include the below
If [Column1] = "Full Name" then [Column2] else null
In this way, we are trying to break the group of rows based on Full Name
Ste2: Filldown the 'Group' column
Step3: Pivot colum1 and select the column2 as values
Step4: Remove the Group Column
Here is the M query:
let
Source = Excel.Workbook(File.Contents("File_Path.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"
Hi @dyabes , it would be better if you share sample excel file of this data . the original table is hard to read
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"
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🙏
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
112 | |
99 | |
69 | |
67 |