Unpivoting columns and multiply rows in multilple files

[Anonymous]

I have this table with name number and some information. But the last columns of information should be in rows refering to each name.

I want to multiply the number of rows by the number of extra columns (the extra columns can vary but the number of unaltered original columns is always 2, name and number) and then unpivot the last two columns so the information is arranged for each row. Like this example.

My problem is that I have dozens of file in which I want to do this and the number of extra columns varies. Is it possible to combine them all without losing information or adding unnecessary rows? If not is there a way to make this process easier to do in all the files?

[Anonymous]

Hi Migukau,

I have taken the below tables to explain the soution step by step.

Step-by-Step Instructions:

1. Load Files into Power Query:

Open Power Query in Excel or Power BI.

Use "Get Data" > "From File" > "From Folder" to load all files from a specific folder.

2. Combine Files:

In the folder dialog, select "Combine & Load" to combine the contents of the files.

Power Query will create a sample file and apply transformations to all files in the folder.

3. Unpivot Columns:

In the Power Query Editor, select the columns you want to keep (e.g., "Name" and "Number").

Right-click on the selected columns and choose "Unpivot Other Columns".

This will unpivot all other columns, converting them into attribute-value pairs.

4. Rename Columns:

Rename the unpivoted columns to something meaningful, such as "Date" and "Value".

Ensure the data types are correctly set (e.g., "Date" for the date column, "Number" for numerical values).

5. Clean Data:

Remove any unnecessary rows or columns.

Check for any errors or inconsistencies in the data.

6. Load Data:

Once the data is cleaned and transformed, click "Close & Load" to load the data back into Excel or Power BI.

This approach ensures that you can handle multiple files efficiently and maintain the necessary data structure.

Regards,

Chiranjeevi Kudupudi

[Anonymous]

I have an M text file with all the transformations I need to perform on the files. Is it possible to automatically apply these transformations to each of the files in the folder?
Also when I try to do this I lose the columns with the date in the second file. I just end up with 1 2 and 3 of january

[Anonymous]

Hi Migukau,

Follow these steps

  1 - Go to "Advanced Editor" and paste the M code from your text file.

  2 - Use a "Function" to apply the transformations.

  3 - Modify the M code to ensure it works on all files in the folder.

  4 - Ensure the transformations correctly handle date columns in each file.

  5 - Check that the column names are consistent across files to avoid losing data.

If date columns are inconsistent, you might need to adjust your code to dynamically handle different column names.

Regards,

Chiranjeevi Kudupudi

[Anonymous]

My code handles column names, but when I load the files into power query the columns dissapear. Its not a problem with the code or the formatting, the problem is that when I load a folder the columns which differ from file to file simply dissapear. The only solutio I have found is to load one file at a time.

[Anonymous]

Can you please post here your column names and M code here with sample tables.
So that it will be more helpful to address your query.

[Anonymous · Accepted solution]

let
    Source = Excel.Workbook(File.Contents("C:\Users\FEM6CLJ\Desktop\Excel\Backup\ASM\Setup_1_L2009#1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    filename = "Setup_1_L2009#1",
    #"Removed Blank Rows" = Table.SelectRows(Sheet1_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Other Columns" = Table.RemoveColumns(#"Removed Blank Rows",{"Column1", "Column6", "Column7", "Column8", "Column9", "Column11", "Column12","Column13","Column14"}),
    #"ColumnsToReplace" = List.Skip(Table.ColumnNames(Sheet1_Sheet), 14),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"?",Replacer.ReplaceValue,#"ColumnsToReplace"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Station", "Location", "Track", "Division", "Component"}, "Part Number", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns","?",null,Replacer.ReplaceValue,{"Value"}),
    #"Added Column" = Table.AddColumn(#"Replaced Value1","File Name", each filename),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Column",{"File Name", "Station", "Location", "Track", "Division", "Component", "Part Number", "Value"})
    
in
    #"Reordered Columns"

I have this M code which works for every file, the only thing that needs to be changed is the filename variable and maybe sheet name. 

Station

Location

Track

Division

Tower

Level

Feeder Type

Fixed Feeder

Component

Fixed Comp.

Recipe Count

Recipe Commonality [%]

All Pickups

1038350528_PB_04

1038350528_PT_04

1038350016_PB_05

Station

Location

Track

Division

Tower

Level

Feeder Type

Fixed Feeder

Component

Fixed Comp.

Recipe Count

Recipe Commonality [%]

All Pickups

1038350016_PB_05

1038350016_PT_05

These are examples of column names in 2 files, when I choose the folder, the 1038350016_PB_05 and the 1038350016_PT_05 columns form the second file dont appear. Also as I have explained before, I have to unpivot these columns so they cannot be concaetenated for the whole folder.