Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Growing Excel Sheet as Data Source

Hello Hello,

I am currently using an exceltable as datasource for PowerBI. The problem I am dealing with is that every month i update the table, a new column with a new month is added between 2 other existing columns. As soon as I copy the table into the source, PowerBI has problems with the new order of the columns and an added month.

Does anyone have n idea how to make it possible to get PowerBI dealing with the "growing" source?

 

Thank you very much!

 

Need Help
Tutorial Requests
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous ,

     

    For this kind of problem, the best way in powerbi is to create the following table via dax. When the column has not changed and the number of rows is updated, just refresh the data source directly; if when the column is added, just add this column name manually in the formula. This formula can be used as a template.

    Table_max = 
var MonthName = DATATABLE("MonthName",STRING,{{"Jan"},{"Feb"},{"Mar"}})
var T_crossjoin = CROSSJOIN(MonthName,Sheet1)
var result = SELECTCOLUMNS(T_crossjoin,"Positon",[Position],"Month",[MonthName],"Value",
SWITCH([MonthName],
"Jan",[Jan],
"Feb",[Feb],
"Mar",[Mar],BLANK()))
return result

    If only row records can be added, just refresh the data source directly:

    If a column record is added, just add this column name to the formula:

    Table_max = 
var MonthName = DATATABLE("MonthName",STRING,{{"Jan"},{"Feb"},{"Mar"},{"Apr"}})
var T_crossjoin = CROSSJOIN(MonthName,Sheet1)
var result = SELECTCOLUMNS(T_crossjoin,"Positon",[Position],"Month",[MonthName],"Value",
SWITCH([MonthName],
"Jan",[Jan],
"Feb",[Feb],
"Mar",[Mar],
"Apr",[Apr],
BLANK()))
return result


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    For this kind of problem, the best way in powerbi is to create the following table via dax. When the column has not changed and the number of rows is updated, just refresh the data source directly; if when the column is added, just add this column name manually in the formula. This formula can be used as a template.

    Table_max = 
var MonthName = DATATABLE("MonthName",STRING,{{"Jan"},{"Feb"},{"Mar"}})
var T_crossjoin = CROSSJOIN(MonthName,Sheet1)
var result = SELECTCOLUMNS(T_crossjoin,"Positon",[Position],"Month",[MonthName],"Value",
SWITCH([MonthName],
"Jan",[Jan],
"Feb",[Feb],
"Mar",[Mar],BLANK()))
return result

    If only row records can be added, just refresh the data source directly:

    If a column record is added, just add this column name to the formula:

    Table_max = 
var MonthName = DATATABLE("MonthName",STRING,{{"Jan"},{"Feb"},{"Mar"},{"Apr"}})
var T_crossjoin = CROSSJOIN(MonthName,Sheet1)
var result = SELECTCOLUMNS(T_crossjoin,"Positon",[Position],"Month",[MonthName],"Value",
SWITCH([MonthName],
"Jan",[Jan],
"Feb",[Feb],
"Mar",[Mar],
"Apr",[Apr],
BLANK()))
return result


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.