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 note that 1st row is a header)

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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super 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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    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.