Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone,
I have some trouble with creating a new table out of this table:
Date | Product | Status |
07.03.2024 | Book | Preparing |
07.03.2024 | Shoes | Preparing |
08.03.2024 | Book | Preparing |
08.03.2024 | Shoes | Delivering |
08.03.2024 | Dress | Preparing |
09.03.2024 | Book | Preparing |
09.03.2024 | Shoes | Delivered |
09.03.2024 | Dress | Delievering |
What I want to get is a table which should count for how many times a product occurs with the corresponding status like this:
Product | Preparing | Delivering |
Book | 3 | |
Shoes | 1 | 1 |
Dress | 1 | 1 |
When I am using a query like this "GroupedRows = Table.Group(#"Changed Type", {"Product", "Status"}, {{"Preparing", each Table.RowCount(TableDistinct(_)), Int64.Type}, {"Delivering", each Table.RowCount(TableDistinct(_)), Int64.Type}}) it counts the Total occurence of the value in the table, so this doesn't help me.
I hope someone could provide me a solution.
Thank you in advance!
Solved! Go to Solution.
I will give one simple way to do it.
Remove Date column
Duplicate product column
Pivot on Status
Select first 3 columns
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Product", "Product - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Product - Copy", List.Count),
#"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Product", "Preparing", "Delivering"})
in
#"Removed Other Columns"
Hi @Ekaterina_ ,
Your previous thought works, just need to filter the Group's table next.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMDJR0lFyys/PBlIBRakFiUWZeemHFijF6qCoCM7ITy1GVgJWYIHbCHR5mAEuqTmZZalIliCpcSlKLS7G4g5LAtZY4rQmNQVdAcwOVHfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Status", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Product"}, {{"Preparing", each Table.RowCount(Table.SelectRows(_, each [Status]="Preparing")), Int64.Type}, {"Delivering", each Table.RowCount(Table.SelectRows(_, each [Status]="Delivering")), Int64.Type}})
in
#"Grouped Rows"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Hi @Ekaterina_ ,
Your previous thought works, just need to filter the Group's table next.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMDJR0lFyys/PBlIBRakFiUWZeemHFijF6qCoCM7ITy1GVgJWYIHbCHR5mAEuqTmZZalIliCpcSlKLS7G4g5LAtZY4rQmNQVdAcwOVHfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Status", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Product"}, {{"Preparing", each Table.RowCount(Table.SelectRows(_, each [Status]="Preparing")), Int64.Type}, {"Delivering", each Table.RowCount(Table.SelectRows(_, each [Status]="Delivering")), Int64.Type}})
in
#"Grouped Rows"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Hello @Anonymous
Thank you very much for the help, with this option it works also perfectly! 😀
I will give one simple way to do it.
Remove Date column
Duplicate product column
Pivot on Status
Select first 3 columns
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Product", "Product - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Product - Copy", List.Count),
#"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Product", "Preparing", "Delivering"})
in
#"Removed Other Columns"
User | Count |
---|---|
11 | |
7 | |
5 | |
5 | |
4 |
User | Count |
---|---|
16 | |
14 | |
8 | |
6 | |
6 |