Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
dyabes
Advocate I
Advocate I

Transform Column1 to ColumnHeaders

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

3 ACCEPTED SOLUTIONS
GreeshmaN
Frequent Visitor

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:

GreeshmaN_0-1732854367499.png

 




View solution in original post

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

ribisht17_0-1732858771149.png

 

 

Ritesh

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

View solution in original post

danextian
Super User
Super User

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"

danextian_0-1732859722114.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

8 REPLIES 8
dyabes
Advocate I
Advocate I

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.

danextian
Super User
Super User

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"

danextian_0-1732859722114.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
GreeshmaN
Frequent Visitor

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:

GreeshmaN_0-1732854367499.png

 




GreeshmaN
Frequent Visitor

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.

Column1Column2
Full NameAbayan, Mark
Loan Release Date11/21/2024
Loan Amount4000
Loan TypeSalary Loan
Loan PurposeSent to parents
Full NameDacanay, Jeffrey
Loan Release Date11/25/2024
Loan Amount1000
Loan TypeCash Advance
Loan PurposeCash Advance
Full NameFlores, Joan
Loan Release Date11/28/2024
Loan Amount500
Loan TypeCash Advance
Loan Purposenull


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"

GreeshmaN_0-1732853950211.png

 




powerbidev123
Advocate II
Advocate II

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

NameColumn1Column2
employee_financial_assistance_markabayan_20241121.xlsxFull NameAbayan, Mark
employee_financial_assistance_markabayan_20241121.xlsxTitleOperations Asst
employee_financial_assistance_markabayan_20241121.xlsxEmploymentContractual
employee_financial_assistance_markabayan_20241121.xlsxLoan Release Date11/21/2024
employee_financial_assistance_markabayan_20241121.xlsxLoan Amount4000
employee_financial_assistance_markabayan_20241121.xlsxLoan TypeSalary Loan
employee_financial_assistance_markabayan_20241121.xlsxLoan PurposeSent to parents
employee_financial_assistance_jeffreydacanay_20241125.xlsxFull NameDacanay, Jeffrey
employee_financial_assistance_jeffreydacanay_20241125.xlsxTitleKitchen Staff
employee_financial_assistance_jeffreydacanay_20241125.xlsxEmploymentFull Time
employee_financial_assistance_jeffreydacanay_20241125.xlsxLoan Release Date11/25/2024
employee_financial_assistance_jeffreydacanay_20241125.xlsxLoan Amount1000
employee_financial_assistance_jeffreydacanay_20241125.xlsxLoan TypeCash Advance
employee_financial_assistance_jeffreydacanay_20241125.xlsxLoan PurposeCash Advance
employee_financial_assistance_joanflores_20241128.xlsxFull NameFlores, Joan
employee_financial_assistance_joanflores_20241128.xlsxTitleKitchen Staff
employee_financial_assistance_joanflores_20241128.xlsxEmploymentFull Time
employee_financial_assistance_joanflores_20241128.xlsxLoan Release Date11/28/2024
employee_financial_assistance_joanflores_20241128.xlsxLoan Amount500
employee_financial_assistance_joanflores_20241128.xlsxLoan TypeCash Advance
employee_financial_assistance_joanflores_20241128.xlsxLoan Purposenull

 

TARGET TABLE

Full NameTitleEmploymentLoan Release DateLoan AmountLoan TypeLoan Purpose
Abayan, MarkOperations AsstContractual11/21/20244000Salary LoanSent to parents
Dacanay, JeffreyKitchen StaffFull Time11/25/20241000Cash AdvanceCash Advance
Flores, JoanKitchen StaffFull Time11/28/2024500Cash Advancenull

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"

Ashish_Mathur_0-1732939515629.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

ribisht17_0-1732858771149.png

 

 

Ritesh

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.