Forum Discussion

Azatus's avatar
Azatus
New Member
3 years ago
Solved

I need help with an awkward data

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 no...
Show and Tell
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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.