Transform Column1 to ColumnHeaders

[dyabes]

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

[GreeshmaN · Accepted 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:

[ribisht17 · Accepted solution]

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🙏

[dyabes]

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 · Accepted solution]

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"

[GreeshmaN · Accepted 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:

[GreeshmaN]

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"

[powerbidev123]

Hi @dyabes , it would be better if you share sample excel file of this data . the original table is hard to read

[dyabes]

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

[Ashish_Mathur]

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"

[ribisht17 · Accepted solution]

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🙏