PowerBI - Complicated issue or Not?

BrettOlbrys1

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
138
Office Version
  1. 365
Platform
  1. Windows
NOTE: All information below is sample data and does not represent any actual customer information.

I am a NEWBIE to PowerBI, so I apologize for this question. I have a data table named "DATA." In the "Data" table are the following columns and rows of data. You will see in the example that some company names repeat and in a row of data, there can be a quantity for a single product or multiple products.


DATA TABLE
Account NameDateFiscal PeriodTotal QtyProduct AProduct BProduct C
AARP1/7/24Q2-20241,2001,200
Affordable Housing2/7/24Q2-2024250250
Affordable Housing2/8/24Q2-2024350100250
Comcast4/26/24Q2-2024300300
Deltek4/27/24Q2-202452049030
General Electric6/28/24Q2-2024700700
General Electric6/29/24Q2-2024300300
General Electric6/30/24Q2-2024550100350100
HP7/2/24Q2-2024600400200

I need to have a drop-down slicer that has "Product A" "Product B" and "Product C." The slicer will impact multiple visuals, but for this example, I want to create the below visual. What I want to happen is if I select Product A, B, or C, only those lines with those Products to appear in the table. But, if I select multiple products, or all products, I want each row to appear in the table showing only one of the products and the row below it will be the other product(s) for that Company and Date.

For example, in the "Data," you see two rows for "Affordable Housing." The first row only has 250 of Product B, but the second row for Affordable Housing has 100 for Product A and 100 for Product C. In the visualization of the data for Affordable Housing, you see three rows of data. The first row for Affordable Housing corresponds to the first row in the data table for Affordable Housing because there is only one product in that row, but rows 2 and 3 for Affordable Housing represent the Product A from the data table and Product C from the data table.

So anytime a row has multiple products, I want that row to duplicate for each product and only show the Qty for that product. All of the other rows with a single product would appear as a single row.

How do I accomplish this?

VISUALIZATION
Account NameActivation DateProduct AProduct BProduct C
AARP1/7/241,200
Affordable Housing2/7/24250
Affordable Housing2/8/24100
Affordable Housing2/8/24250
Comcast4/26/24300
Deltek4/27/2449030
General Electric6/28/24700
General Electric6/29/24300
General Electric6/30/24100
General Electric6/30/24350
General Electric6/30/24100
HP7/2/24400
HP7/2/24200
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    tbl = Table.RemoveColumns(ChangeType,{"Fiscal Period", "Total Qty"}),
    ProductNames = List.Buffer(List.Skip(Table.ColumnNames(tbl), 2)),
    tbl1 = Table.AddColumn(tbl, "Rcd", each 
        [   lst = List.Zip({List.LastN(Record.ToList(_), 3), ProductNames}), 
            r = List.Accumulate(lst, {}, (s,c)=> 
                if c{0}<>null then s & {Record.AddField([],c{1},c{0})} else s)  ]
        [r]  ),
    ExpandRcd = Table.ExpandListColumn(tbl1, "Rcd"),
    RemoveColumns = Table.RemoveColumns(ExpandRcd,ProductNames),
    Result = Table.ExpandRecordColumn(RemoveColumns, "Rcd", ProductNames)
in
    Result

Book1
ABCDEFGHIJKLMN
1DATAQuery output
2Account NameDateFiscal PeriodTotal QtyProduct AProduct BProduct CAccount NameDateProduct AProduct BProduct C
3AARP1/7/2024Q2-202412001200AARP1/7/20241200
4Affordable Housing2/7/2024Q2-2024250250Affordable Housing2/7/2024250
5Affordable Housing2/8/2024Q2-2024350100250Affordable Housing2/8/2024100
6Comcast4/26/2024Q2-2024300300Affordable Housing2/8/2024250
7Deltek4/27/2024Q2-202452049030Comcast4/26/2024300
8General Electric6/28/2024Q2-2024700700Deltek4/27/2024490
9General Electric6/29/2024Q2-2024300300Deltek4/27/202430
10General Electric6/30/2024Q2-2024550100350100General Electric6/28/2024700
11HP7/2/2024Q2-2024600400200General Electric6/29/2024300
12General Electric6/30/2024100
13General Electric6/30/2024350
14General Electric6/30/2024100
15HP7/2/2024400
16HP7/2/2024200
17
Sheet4
 
Upvote 0
JGordon11 said:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    tbl = Table.RemoveColumns(ChangeType,{"Fiscal Period", "Total Qty"}),
    ProductNames = List.Buffer(List.Skip(Table.ColumnNames(tbl), 2)),
    tbl1 = Table.AddColumn(tbl, "Rcd", each
        [   lst = List.Zip({List.LastN(Record.ToList(_), 3), ProductNames}),
            r = List.Accumulate(lst, {}, (s,c)=>
                if c{0}<>null then s & {Record.AddField([],c{1},c{0})} else s)  ]
        [r]  ),
    ExpandRcd = Table.ExpandListColumn(tbl1, "Rcd"),
    RemoveColumns = Table.RemoveColumns(ExpandRcd,ProductNames),
    Result = Table.ExpandRecordColumn(RemoveColumns, "Rcd", ProductNames)
in
    Result

Book1
ABCDEFGHIJKLMN
1DATAQuery output
2Account NameDateFiscal PeriodTotal QtyProduct AProduct BProduct CAccount NameDateProduct AProduct BProduct C
3AARP1/7/2024Q2-202412001200AARP1/7/20241200
4Affordable Housing2/7/2024Q2-2024250250Affordable Housing2/7/2024250
5Affordable Housing2/8/2024Q2-2024350100250Affordable Housing2/8/2024100
6Comcast4/26/2024Q2-2024300300Affordable Housing2/8/2024250
7Deltek4/27/2024Q2-202452049030Comcast4/26/2024300
8General Electric6/28/2024Q2-2024700700Deltek4/27/2024490
9General Electric6/29/2024Q2-2024300300Deltek4/27/202430
10General Electric6/30/2024Q2-2024550100350100General Electric6/28/2024700
11HP7/2/2024Q2-2024600400200General Electric6/29/2024300
12General Electric6/30/2024100
13General Electric6/30/2024350
14General Electric6/30/2024100
15HP7/2/2024400
16HP7/2/2024200
17
Sheet4
Click to expand...
Thank you. So are you saying that I should be doing the conversion in Excel, and the output should be new "Data" table to be used in PowerBI?
 
Upvote 0
BrettOlbrys1 said:
Thank you. So are you saying that I should be doing the conversion in Excel, and the output should be new "Data" table to be used in PowerBI?
Click to expand...
No, I just used an excel data table as my Source. Change the Source step to reference your data table.
 
Upvote 0
You must log in or register to reply here.

Similar threads

RICH937
  • Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
413
RICH937
RICH937
S
  • Solved
Pivot data returning earliest date and latest date (only) counts by a training class number
2
Replies
14
Views
1K
SerenityNetworks
S
S
  • Question
Matrix Visual in Power BI
Replies
2
Views
151
shansakhi
S
S
  • Question
PowerBI Measure to calculate Sales Goals with Reports To: structure.
Replies
1
Views
254
Matt Allington
M
A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
277
ashish128
A

Forum statistics

Threads
1,221,526
Messages
6,160,340
Members
451,637
Latest member
hvp2262

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top