I need help with an awkward data

[Azatus]

So I have this dataset from google sheets. Link to the sheet
How can I export this correctly so I can build dashboards from it? It also should update everytime someone changes a document. (Please note that 1st row is a header)

[v-yanjiang-msft · Accepted solution]

Hi @Azatus ，

There's a Google Sheets connector in Power BI Desktop.

In Desktop, once the data source is changed, directly click the Refresh button, data will update then.

In Service, you can configure a schedule refresh which can refresh the dataset daily, if you wnat to refresh the dataset once the data source is changed, you can put the sheet in SharePoint, and create a Power automate flow as below:

Best regards,

Community Support Team_yanjiang

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

[lbendlin]

Something like this  (replace the source accordignly)

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\sales.xlsx"), null, true),
    Лист1_Sheet = Source{[Item="Лист1",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.FirstN(Лист1_Sheet,1) & Table.Skip(Лист1_Sheet,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Июль", "Июль.1"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Статус кофейни", "Инвестор", "ID", "Тип объекта", "Кагорта abc", "Релокация (к-во) ", "Дата запуска", "Адрес"})
in
    #"Filled Down"

Note that you don't want to have that many columns - you should unpivot all the date columns.

It also should update everytime someone changes a document

Not possible with an import mode data source. Not sure if Power Automate can subscribe to Google Sheet changes.