Forum Discussion

ToughTake's avatar
ToughTake
Frequent Visitor
2 years ago
Solved

Data Layout Advice

Hi,

 

I am pulling a lot of data into Power BI from Online Excel sheets, and am finding it hard to lay the data our in a way that Power BI can use it. hopefully this is just be being new...I am hoping someone will be able to help me figure this out.

 

For example: https://imgur.com/veylHFk

 

What I would like to do, is to be able to add a filter in Power BI to select the week, however as the weeks are across multiple columns I cannot do this? If I add weeks into a single column then I will have to have my suppliers listed for each week of the year, with over 300 suppliers list ends up being a very long sheet.

 

For example: https://imgur.com/gtUunGw

 

Is there a better way for me to do this?

 

Thanks!

 

 

 

 

General Comment
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    In the Power Query Editor, right clock on the first 2 columns and select "Unpivot Other Columns".  Split the attribute column into 2 using space as a delimiter.  The data should not become usable.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    In the Power Query Editor, right clock on the first 2 columns and select "Unpivot Other Columns".  Split the attribute column into 2 using space as a delimiter.  The data should not become usable.

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello ToughTake ,  and thank you for sharing a question with the Community.  This reply is informational. Please follow the decorum of the Community Forum when asking a question.

    Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

     

    If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

  • ToughTake's avatar
    ToughTake
    Frequent Visitor
    2 years ago

    Hi foodd 

     

    Apologise, and thanks for the reply.

     

    An example excel file is here: https://filebin.net/qmhliy5npb7x9jpl

     

    I guess all I am looking for really is how I can load this into PowerBi and be able to add a week date filter so that I can show one week at a time.

     

    Thank you