Help with data exported from Excel

[Anonymous]

Hello. I'm having a problem with one of my BI's and i didnt't find how to solve it. Can someone help me?

Well, i've got the following data (Those are the companys that have signed up and progressed through our startup program, we had one edition every year since 2020).

I need to somehow be able to select then individually and see how many we've got in certain edition and phase. But when i select for ex: edição 2020 and edição 2023, it will filter by the ones that have participated in both, but i need it to be the ones that have signed up in 2020 plus the ones in 2023. I'm using a excel sheet as the data base where every one of those columns are also single columns in Excel.

[pmreis · Accepted solution]

Hello @Anonymous 

Transform your data from the original format to the following one:

1) Unpivot all columns except participant

2) Split Year and Event by delimiter (ex: "Edição 2020" -> "Edição",  "2020")

3) Rename columns and change data types

Here is a sample Power Query code (it uses Enter Data for demo purposes, replace your "source" to connect to your data)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSwYNjdaKVjKA8A6wYpMIYTZ8BChukwgSLXoRKkApTDH3ItsbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Participante = _t, #"Edição 2020" = _t, #"Pitch 2020" = _t, #"Aceleração 2020" = _t, #"Edição 2021" = _t, #"Pitch 2021" = _t, #"Imersão 2021" = _t, #"Aceleração 2021" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Participante"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Evento"}, {"Attribute.2", "Ano"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Ano", Int64.Type}, {"Value", Int64.Type}})
in
#"Changed Type"

This way you will be able to filter freely by event and years and find the participants with value 1 or Yes

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

[Anonymous]

Ty so much, i just had to do some trials and error with the code you gave me and it worked. Also, u have explained it amazingly well, so it was easy to get.

Have a nice week, ty.

[Martin_D]

Hi @Anonymous ,

Would you like to share a file pbix file that demonstartes the decribed behavior so we can see how you solved this in Power BI? Preferably also share the Excel source, so we can modify and refresh the queries in Power BI.

I'm not sure what your data model looks like, but default behavior of Power BI is that if you select two category values in a filter, then all data belonging to the one or to the other value appears on the report page.

Did you unpivot the Excel table when loading it into Power BI, so that the column titles in Excel become one column in the Power BI dataset, that then unfolds again into columns in your visual by using it in the columns configuration of a matrix visual (the Power BI version of a pivot table)? Then also the filter should work as required.

If you want to learn more about data modeling fundamentals I'd recommend reading this chapter of the Power BI documentation: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

BR

Martin