Unpivot wide-growing table

[Anonymous]

Dear all, 

I have a csv file coming from a system I cant control. 

Every week  a new column  of the same type of data (Let say weekly sales) is added making the file growing "wide-wize".  

I know how to do unpivot to get a long and slim table to conduct my visualization tasks and it my current approach.

However, as the columns are addedd every week, I need to open the query and change the range of the columns to be used in the unpivot operations. Let say From (col1 to col 10 last week will become From col1 to 11 this week)

I want to automate this task so that every week the newly added column is automatically added to unpivot range. 

Is there anyway to make this happen?

I will appreciate your help

Best regards,

Koffi

[v-juanli-msft · Accepted solution]

Hi @Anonymous

For your requirement, code below can help

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.13\first query.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Best Regards

Maggie

[Anonymous]

@v-juanli-msft

Thank you very much. This solve my problem! 

Best regards,

Koffi