Unifiying ever increasing columm numbers to a single data source

[Emrecan]

Hello Community

I have a situation where each month a new table of data is delivered by email to company.

The table contains columms for that months specific sales info. So each time the report is recieved there is an extra columm added to the table for that month. 

İf the table is recieved on the 1st month there is one columm if it is recieved on the 3rd month there are 3 columms on the data.

So the number of columms increases each month. 

File sent on the 1 month

1-2022

13

48

24

File sent on the 2 month

1-2022	2-2022
13	5
48	9
24	11

File sent on the 3 month

1-2022	2-2022	3-2022
13	5	9
48	9	8
24	11

Since there is absoltly no way to reach a row bases data the only option we have is the recive the file transform it to a row format (unpivoting these columms)

is there a way to diynamicly make power querry read the only the last file stored  and do the transformation as the columm number increases each month

thanks 

[Emrecan]

Hello

No ı was actully changing the file name when copying and pasting the new excel file. The problem arises when I do the transformation on power querry say on the 2 month there are 99 columms. When an updated file is added let say the most uptodate one for the 3 month there are now 100 columms. So power querry gives error saying columm number 100 does not exist. Like ı said each new excel file has +1 extra columm

thanks 

[Vijay_A_Verma]

I need to see your query. Better would be with a sample dummy Excel file. Then I can make your steps dyanamic which would be agnostic to number of columns. 

If this is a matter of unpivot only as your first step, then following command will unpivot all columns leaving you with only 2 columns irrespective of number of columns

In your sample file

= Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value")

[Emrecan]

Hello Vijay

The Querry fails due to the number of columms

I think I can explain better with SS's. 1,2,3 represent year of months. New files are added each month.

01-2022_PC

02-2022_PC

03-2022_PC

as such the number of columms to be unpivoted increase each month. That is the only step required 

unpivoting the columms. The goal is to turn the data into row format so that it can be appended to the back data.

There are other steps prior to unpivoting as well

The idea to read the only latest file will work. But this wont

= Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value") because for the latest file for example for the next month there will be 4 columms not 3

the querry is below 

let
Source = Excel.Workbook(File.Contents("C................3-2022_PC.xlsx"), null, true),
DATA_Sheet = Source{[Item="DATA",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(DATA_Sheet,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type any}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Satır ID", Int64.Type}, {"Marka / Marka", type text}, {"Model / Model", type text}, {"Alt Model / Alt Model", type text}, {"Görünen Ad / Versiyon İsmi", type text}, {"Donanım Adı / Donanım Adı", type text}, {"Ülke / Üretim Kaynağı", type text}, {"Ülke / Ülke", type text}, {"Model Yılı / Model Yılı", Int64.Type}, {"Gövde Tipi / Gövde Tipi", type text}, {"Gövde Tipi / Segment", type text}, {"Motor / Tipi", type text}, {"Motor / Hacmi (cc)", Int64.Type}, {"Motor / Silindir Sayısı", Int64.Type}, {"Motor / Silindir İptali", type text}, {"Motor / Start/Stop", type text}, {"Motor / Turbo", type text}, {"Motor / Hibrit Tipi", type text}, {"Elektrik Motoru / Maksimum Güç (Kw)", Int64.Type}, {"Elektrik Motoru / Maksimum Tork (Nm)", Int64.Type}, {"Elektrik Motoru / kWsa/100 kmde Güç Tüketimi", type text}, {"Elektrik Motoru / Maksimum Toplam Menzil (km)", type text}, {"Elektrik Motoru / Batarya Kapasitesi (Ah)", type text}, {"Elektrik Motoru / Batarya Şarj Süresi (sa) (Plugin)", type text}, {"Güç / Maksimum Güç (kW)", type text}, {"Güç / Maksimum Gücün Alındığı Devir (kW)", type text}, {"Güç / Maksimum Gücün Alındığı Devir (kW) (2. Deger)", type text}, {"Güç / Maksimum Güç (PS)", Int64.Type}, {"Güç / Maksimum Gücün Alındığı Devir (PS)", Int64.Type}, {"Güç / Maksimum Gücün Alındığı Devir (PS) (2. Deger)", Int64.Type}, {"Güç / Maksimum Tork (Nm)", Int64.Type}, {"Güç / Maksimum Torkun Alındığı Devir", Int64.Type}, {"Güç / Maksimum Torkun Alındığı Devir (2. Deger)", Int64.Type}, {"Güç / Maksimum Güç (hp)", Int64.Type}, {"Güç / Maksimum Gücün Alındığı Devir (hp)", Int64.Type}, {"Güç / Maksimum Gücün Alındığı Devir (hp) (2. Deger)", Int64.Type}, {"Güç / Hybrid Sistem Toplam Güç (Kw)", Int64.Type}, {"Performans / Maksimum Hız (km/s)", Int64.Type}, {"Performans / Hızlanma 0-100km (s)", type number}, {"Yakıt / Tipi", type text}, {"Yakıt / Yakıt Deposu Hacmi (lt)", type number}, {"Yakıt Tüketimi / Şehiriçi (lt/100km)", type number}, {"Yakıt Tüketimi / Şehirdışı (lt/100km)", type number}, {"Yakıt Tüketimi / Ortalama (lt/(100km)", type number}, {"Emisyon Kontrol Seviyesi / Euro Norm Seviyesi", type text}, {"Emisyon Kontrol Seviyesi / CO2 Seviyesi (g/km ortalama)", Int64.Type}, {"Şanzıman / Şanzıman Tipi", type text}, {"Şanzıman / Vites Adedi", Int64.Type}, {"Şanzıman / Kavrama", type text}, {"Süspansiyon / Ön Süspansiyon Tipi", type text}, {"Süspansiyon / Arka Süspansiyon Tipi", type text}, {"Süspansiyon / Viraj Denge Çubuğu", type text}, {"Çekiş Tipi / Çekiş Tipi", type text}, {"Arazi Yetenekleri / Arkadan İtiş", type text}, {"Arazi Yetenekleri / Önden Çekiş", type text}, {"Arazi Yetenekleri / 4x2 Yaklaşma Açısı", type text}, {"Arazi Yetenekleri / 4x2 Uzaklaşma Açısı", type text}, {"Arazi Yetenekleri / 4x2 Rampa Açısı", type text}, {"Arazi Yetenekleri / 4x2 Su Geçiș Yüksekliği (mm)", type text}, {"Arazi Yetenekleri / 4x4 Yaklaşma Açısı", type number}, {"Arazi Yetenekleri / 4x4 Uzaklaşma Açısı", type number}, {"Arazi Yetenekleri / 4x4 Rampa Açısı", type number}, {"Arazi Yetenekleri / 4x4 Su Geçiș Yüksekliği (mm)", Int64.Type}, {"Arazi Yetenekleri / Arazi Seçimi", type text}, {"Arazi Yetenekleri / Kullanırken Seçme İmkanı", type text}, {"Arazi Yetenekleri / Diferansiyel Kilidi", type text}, {"Arazi Yetenekleri / Sınırlı Kaymalı Diferansiyel", type text}, {"Frenler / Ön Fren Tipi", type text}, {"Frenler / Arka Fren Tipi", type text}, {"Dış Ölçüler / Uzunluk (mm)", Int64.Type}, {"Dış Ölçüler / Genişlik (mm) (Aynalar Hariç)", Int64.Type}, {"Dış Ölçüler / Yükseklik (mm)", Int64.Type}, {"Dış Ölçüler / Dingil Mesafesi (mm)", Int64.Type}, {"Dış Ölçüler / Dönüş Çapı (m)", type number}, {"Dış Ölçüler / Yerden Yükseklik (mm)", Int64.Type}, {"Bagaj Hacmi / Bagaj Hacmi (lt)", Int64.Type}, {"Bagaj Hacmi / Maksimum Bagaj Hacmi (Arka koltuk yatık, tavana kadar (lt)", Int64.Type}, {"Ağırlıklar / Boş Ağırlık Sürücü Dahil (kg)", Int64.Type}, {"Ağırlıklar / Azami Yüklü Ağırlık (kg)", Int64.Type}, {"Tüm Araç Garantisi / Süre (ay)", Int64.Type}, {"Tüm Araç Garantisi / Sınırsız KM Garantisi", type text}, {"Tüm Araç Garantisi / Mesafe (km)", Int64.Type}, {"Kapı Adedi / Kapı Adedi", Int64.Type}, {"Koltuk Adedi / Oturma Kapasitesi (1+Kişi Sayısı)", Int64.Type}, {"Piyasa Giriş / Çıkış / Piyasaya Giriş", Int64.Type}, {"Piyasa Giriş / Çıkış / Piyasadan Çıkış", type text}, {"1-2022", Int64.Type}, {"2-2022", Int64.Type}, {"3-2022", Int64.Type}, {"Toplam", Int64.Type}, {"Year", Int64.Type}, {"Vehicle", type text}, {"Segment", type text}, {"Chassis", type text}, {"Tavsiye Edilen Fiyat#(lf)3-2022", Int64.Type}, {"Tavsiye Edilen Kampanyalı Fiyat#(lf)3-2022", type text}, {"Para Birimi#(lf)3-2022", type text}, {"Kampanya Bilgisi#(lf)3-2022", type text}, {"Türetildiği Satır", Int64.Type}, {"Türetme Sebebi", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Tavsiye Edilen Fiyat#(lf)3-2022", "Tavsiye Edilen Fiyat"}, {"Tavsiye Edilen Kampanyalı Fiyat#(lf)3-2022", "Tavsiye Edilen Kampanyalı Fiyat"}, {"Para Birimi#(lf)3-2022", "Para Birimi"}, {"Kampanya Bilgisi#(lf)3-2022", "Kampanya Bilgisi"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "SEG+GövdeTipi", each [Segment]&"-"&[#"Gövde Tipi / Gövde Tipi"]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Toplam"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Satır ID", "Marka / Marka", "Model / Model", "Alt Model / Alt Model", "Görünen Ad / Versiyon İsmi", "Donanım Adı / Donanım Adı", "Ülke / Üretim Kaynağı", "Ülke / Ülke", "Model Yılı / Model Yılı", "Gövde Tipi / Gövde Tipi", "Gövde Tipi / Segment", "Motor / Tipi", "Motor / Hacmi (cc)", "Motor / Silindir Sayısı", "Motor / Silindir İptali", "Motor / Start/Stop", "Motor / Turbo", "Motor / Hibrit Tipi", "Elektrik Motoru / Maksimum Güç (Kw)", "Elektrik Motoru / Maksimum Tork (Nm)", "Elektrik Motoru / kWsa/100 kmde Güç Tüketimi", "Elektrik Motoru / Maksimum Toplam Menzil (km)", "Elektrik Motoru / Batarya Kapasitesi (Ah)", "Elektrik Motoru / Batarya Şarj Süresi (sa) (Plugin)", "Güç / Maksimum Güç (kW)", "Güç / Maksimum Gücün Alındığı Devir (kW)", "Güç / Maksimum Gücün Alındığı Devir (kW) (2. Deger)", "Güç / Maksimum Güç (PS)", "Güç / Maksimum Gücün Alındığı Devir (PS)", "Güç / Maksimum Gücün Alındığı Devir (PS) (2. Deger)", "Güç / Maksimum Tork (Nm)", "Güç / Maksimum Torkun Alındığı Devir", "Güç / Maksimum Torkun Alındığı Devir (2. Deger)", "Güç / Maksimum Güç (hp)", "Güç / Maksimum Gücün Alındığı Devir (hp)", "Güç / Maksimum Gücün Alındığı Devir (hp) (2. Deger)", "Güç / Hybrid Sistem Toplam Güç (Kw)", "Performans / Maksimum Hız (km/s)", "Performans / Hızlanma 0-100km (s)", "Yakıt / Tipi", "Yakıt / Yakıt Deposu Hacmi (lt)", "Yakıt Tüketimi / Şehiriçi (lt/100km)", "Yakıt Tüketimi / Şehirdışı (lt/100km)", "Yakıt Tüketimi / Ortalama (lt/(100km)", "Emisyon Kontrol Seviyesi / Euro Norm Seviyesi", "Emisyon Kontrol Seviyesi / CO2 Seviyesi (g/km ortalama)", "Şanzıman / Şanzıman Tipi", "Şanzıman / Vites Adedi", "Şanzıman / Kavrama", "Süspansiyon / Ön Süspansiyon Tipi", "Süspansiyon / Arka Süspansiyon Tipi", "Süspansiyon / Viraj Denge Çubuğu", "Çekiş Tipi / Çekiş Tipi", "Arazi Yetenekleri / Arkadan İtiş", "Arazi Yetenekleri / Önden Çekiş", "Arazi Yetenekleri / 4x2 Yaklaşma Açısı", "Arazi Yetenekleri / 4x2 Uzaklaşma Açısı", "Arazi Yetenekleri / 4x2 Rampa Açısı", "Arazi Yetenekleri / 4x2 Su Geçiș Yüksekliği (mm)", "Arazi Yetenekleri / 4x4 Yaklaşma Açısı", "Arazi Yetenekleri / 4x4 Uzaklaşma Açısı", "Arazi Yetenekleri / 4x4 Rampa Açısı", "Arazi Yetenekleri / 4x4 Su Geçiș Yüksekliği (mm)", "Arazi Yetenekleri / Arazi Seçimi", "Arazi Yetenekleri / Kullanırken Seçme İmkanı", "Arazi Yetenekleri / Diferansiyel Kilidi", "Arazi Yetenekleri / Sınırlı Kaymalı Diferansiyel", "Frenler / Ön Fren Tipi", "Frenler / Arka Fren Tipi", "Dış Ölçüler / Uzunluk (mm)", "Dış Ölçüler / Genişlik (mm) (Aynalar Hariç)", "Dış Ölçüler / Yükseklik (mm)", "Dış Ölçüler / Dingil Mesafesi (mm)", "Dış Ölçüler / Dönüş Çapı (m)", "Dış Ölçüler / Yerden Yükseklik (mm)", "Bagaj Hacmi / Bagaj Hacmi (lt)", "Bagaj Hacmi / Maksimum Bagaj Hacmi (Arka koltuk yatık, tavana kadar (lt)", "Ağırlıklar / Boş Ağırlık Sürücü Dahil (kg)", "Ağırlıklar / Azami Yüklü Ağırlık (kg)", "Tüm Araç Garantisi / Süre (ay)", "Tüm Araç Garantisi / Sınırsız KM Garantisi", "Tüm Araç Garantisi / Mesafe (km)", "Kapı Adedi / Kapı Adedi", "Koltuk Adedi / Oturma Kapasitesi (1+Kişi Sayısı)", "Piyasa Giriş / Çıkış / Piyasaya Giriş", "Piyasa Giriş / Çıkış / Piyasadan Çıkış", "Year", "Vehicle", "Segment", "Chassis", "Tavsiye Edilen Fiyat", "Tavsiye Edilen Kampanyalı Fiyat", "Para Birimi", "Kampanya Bilgisi", "Türetildiği Satır", "Türetme Sebebi", "SEG+GövdeTipi"}, "Attribute", "Value")
in
#"Unpivoted Columns"

Hope this can help

Thanks

[Vijay_A_Verma]

Few confirmations needed

1. Are columns till CH fixed every month otherwise they will break your prior steps as well?

2. Should I assume that all columns having the pattern "number-" i.e. 1-2022, 2-2022 will need to be unpivoted?

[Emrecan]

Hello Vijay

-Yes all columms prior to the 1-2022 2-2022 .... are fixed and identical

-Yes all colummss labeled 1-2022, 2-2022 will need to be unpivoted so they can be turned into row data

thanks 

[Vijay_A_Verma · Accepted solution]

Use this for your Unpivot step

Table.Unpivot(#"Removed Columns", List.RemoveNulls(List.Transform(Table.ColumnNames(#"Removed Columns"),each try if List.Contains(List.Numbers(1,12),Number.FromText(Text.Split(_,"-"){0})) then _ else null otherwise null)), "Attribute", "Value")

[Emrecan]

Hello Vijay

Looking at the unpivot code  I was trying to understandt it to use it for other instances.

what is _    in the code here (Text.Split(_,"-"){0})). From what I can gather it means split the current with delimeter "-" and take the first columm?. I couldnt find this kind of usage of text split in documantation. 

thanks 

[Vijay_A_Verma]

_ means current which you have rightly mentioned. 

Text.Split(_,"-") will split 1-2022 into two parts - 1 and 2022 > {0} will pickup 1st element which is 1 here. In next round, it will be applied on 2-2022 and so on. 

This is little bit advanced but as you move on in your PQ journey, you will understand and implement these. 

[Emrecan]

Hello Vijay

Yes  it works now. However reading from the last file on the folder wasnt part of my original querry. How can we combine both?

thanks 

[Vijay_A_Verma]

I want to know if you know the concept of importing from folder and how to manipulate Transform file. Accordingly, I will draft my response.

[Emrecan]

Hello Vijay

I am at best novice in power bi and no experience with M 

thanks 

[Vijay_A_Verma]

Importing from a folder requires some prior knowledge of M as you would be required to make the changes in Transform file.

My recommendation would be that you should see this video and practice importing files from a folder. Then you can come back. Then you will be able to follow the solution which I will give you.

[Emrecan]

Hello

I actually did it myself by using the filter and top row option on power querry to get the most up to date file and remove the rest. Then I used your unpivot command to unpivot the columms. Yes it works dynamicly doesnt matter if there is 2 columms or 3 it unpivots them all regardles. The only step it fails is when there is a type change where the names and number of columms. I'll dig deeper to understand your dynamic unpivot step 🙂

Your solution is welcomed as well if you can share it 

Now I can merge this to back data 🙂

thanks 

[Vijay_A_Verma]

Change type step will have to removed from everywhere as Change type hard codes the column names. 

Please remove chane type step and let me know if you still face the issue. 

[Emrecan]

Hello Vijay

for reading the last file on the folder when clicked on the binary 

a referenace is created to the file name. So if a new file is draged or dropped in the folder the querry fails

for the unpivot action. The error persist and is generated from the columm number

thanks 

[Vijay_A_Verma]

Delete this step from your query. This is not needed. 

[Vijay_A_Verma]

I presume you are using folder connector. 

When you connect to a folder and choose transform button, you can issue following command to select the last file only.

= Table.SelectRows(Source, each ([Date created] = List.Max(Source[Date created])))