Show All Dates one item was sold

[anonymous123141]

Hello,

I am having issues listing all dates a particular item sold.

The following table represents the data loaded to Power BI via SQL.

I want to represent quickly all the dates a particular Item was sold.  I am missing something because I can only pull the first date.  I need something like the following.

Is there a way to accomplish this in Power BI?  

Thanks!

[rohit_singh]

Hi @anonymous123141 ,

Thanks for sending across the data. I have a potential solution for you using Power Query.

Here is the M-code :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdXBbhoxEAbgd+GcKR57xh4fba+tqu0pV5TDqkENUkgqglqpT18vqCo0u+m2sHBBFiB9+nd2/C8WM2O9n93M4qa9f3lov9cjz1HNtdK6nj+131pjta1HVPXjcbVdbl66P1H38+3y8/N6vXy6b7er56f6hZUQilEIzuUGyIqAD2gAlc2JHUfhPLu7WcwsezpmaS49KquO/bJp152qndOqzw0xoG1yguwDAwVbwIvyQFojKW5MiXbnMov8GXiMbLxTvXKMMUVdGBovoSZGBV6TAiYnisiRNmUnk7D7d5cM9ydOnDGx9cDoDVBSDnzOGVwWG71WuXi3c98ecHlcfTXUjQL5cL6Cp81Xk3Rpbz/uPffL+9Cuf+wOdSyHHorn/pyVbApbQI4WyCgGMTl0k5VsCEPCtJ+sELsuyPuUBlH1Th+pmqUPxWhi5GChsYhAuWnA1/lBIyoaCcK6jEeNPkpqPKle1FHwtikESVTNl0OAaFIBFxvKkoWNlrOjntE45wOkIrkOtG5PzRghaPRJUaoW/n1hh1h0jibd1yFY80De86zrIOuE6aRtffMxx3b7sF9WOlJ1766e6xkPqAYnfMBD5sAdcZ678Dd6fElgLzr6KuxF7RUatrLmShU7ip6iY0fBE5WsvXDJ+muU7GuUjla2vsa95AkV+5q8QMX+P3pCxR6+vZft2DHyFCU7yp2gZQ/dC9bsCPb8PTsGnaJo7ZRFe/cT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer #" = _t, Warehouse = _t, EventDate = _t, #"Item Name" = _t, Size = _t, UoM = _t, #"Total Amount" = _t, #"App/Rec" = _t, LayerName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer #", Int64.Type}, {"Warehouse", type text}, {"EventDate", type text}, {"Item Name", type text}, {"Size", type number}, {"UoM", type text}, {"Total Amount", Int64.Type}, {"App/Rec", type text}, {"LayerName", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Customer #", "EventDate", "Item Name"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Customer #", "Item Name"}, {{"Count", each _, type table [#"Customer #"=nullable number, EventDate=nullable text, Item Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][EventDate]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.1", each List.Distinct([Custom])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Values", List.Distinct(#"Extracted Values"[#"Item Name"]), "Item Name", "Custom.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"Lava3626", "Flip344", "Jamz22", "Bath344"})
in
    #"Replaced Value"

and here is the result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[anonymous123141]

@rohit_singh 

Thank you so very much!  I am coming from a SQL database, it is not working for me.  I have adjusted the code as such.  do you see where I made a mistake?

let
Source = Sql.Database("ricetec-prod.database.windows.net", "agX-dev"), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FieldID" = _t, Grower = _t, EventDate = _t, #"ChemUsed" = _t, AppliedRate = _t, Applied Rate Units = _t, #"Acres" = _t, #"App/Rec" = _t, LayerName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FieldID", Int64.Type}, {"Grower", type text}, {"EventDate", type text}, {"ChemUsed", type text}, {"Applied Rate", type number}, {"Applied Rate Units", type text}, {"Acres", Int64.Type}, {"App/Rec", type text}, {"LayerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"FieldID", "EventDate", "Grower"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"FieldID #", "Grower"}, {{"Count", each _, type table [#"FieldID #"=nullable number, EventDate=nullable text, ChemUed=nullable text]}}),

in
#"ReplaceValue"

[rohit_singh · Accepted solution]

Hi @anonymous123141 ,

I cannot access your datasource so unfortunately I cannot load any data. However, there couple of mistakes at the end of the script that I can see right away which I have highlighted in red below :

let
Source = Sql.Database("ricetec-prod.database.windows.net", "agX-dev"), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FieldID" = _t, Grower = _t, EventDate = _t, #"ChemUsed" = _t, AppliedRate = _t, Applied Rate Units = _t, #"Acres" = _t, #"App/Rec" = _t, LayerName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FieldID", Int64.Type}, {"Grower", type text}, {"EventDate", type text}, {"ChemUsed", type text}, {"Applied Rate", type number}, {"Applied Rate Units", type text}, {"Acres", Int64.Type}, {"App/Rec", type text}, {"LayerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"FieldID", "EventDate", "Grower"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"FieldID #", "Grower"}, {{"Count", each _, type table [#"FieldID #"=nullable number, EventDate=nullable text, ChemUed=nullable text]}}),

in
#"ReplaceValue" #"Grouped Rows"

Lose the comma at the end of the grouped rows step and the end should be #"Grouped Rows" and not 

#"ReplaceValue"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[rohit_singh]

Hi @anonymous123141 ,

Could you please provide sample data in text format (and not screenshot) so that I might be able to help you with a solution?

Kind regards,

Rohit

[anonymous123141]

@rohit_singh 

Thank you.  Here is the data

Spoiler

 

Customer #	Warehouse	EventDate	Item Name	Size	UoM	Total Amount	App/Rec	LayerName
3699	Bradshaw	5/10/2022	Lava3626	10	liters	542	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E
6594	Bradshaw	4/8/2022	Lava3626	500	grams	27720	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	4/8/2022	Lava3626	500	grams	39700	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	4/8/2022	Lava3626	500	grams	43520	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
3699	Bradshaw	5/10/2022	Flip344	15	liters	812	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E
2487	RK	5/17/2022	Jamz22	140	liters	18950	Recommendation	C68ADF56-15B6-4305-83EA-4228E341AC1C
584574	HCC	5/17/2022	Jamz22	10.2	liters	1258	Recommendation	1B3BB5A6-D611-4EDD-9485-D80B38A852FC
584574	HCC	5/17/2022	Jamz22	320	liters	39408	Recommendation	74A96DF4-C80A-4EAA-B3CF-7BD4E8E85328
584574	HCC	5/17/2022	Jamz22	320	liters	39408	Recommendation	9513779A-CF8E-46A5-80BB-A219C04C5841
6594	Bradshaw	4/8/2022	Jamz22	320	liters	17740	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	4/8/2022	Jamz22	320	liters	25408	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	4/8/2022	Jamz22	320	liters	27854	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
6594	Bradshaw	4/8/2022	Bath344	4	liters	222	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	4/8/2022	Bath344	4	liters	318	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	4/8/2022	Bath344	4	liters	350	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
3699	Bradshaw	5/10/2022	Bath344	2	liters	110	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E
3699	Bradshaw	5/16/2022	Lava3626	10	liters	542	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E
6594	Bradshaw	5/13/2022	Lava3626	500	grams	27720	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	5/13/2022	Lava3626	500	grams	39700	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	5/13/2022	Lava3626	500	grams	43520	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
3699	Bradshaw	5/16/2022	Flip344	15	liters	812	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E
2487	RK	5/19/2022	Jamz22	140	liters	18950	Recommendation	C68ADF56-15B6-4305-83EA-4228E341AC1C
584574	HCC	5/19/2022	Jamz22	44	liters	5420	Recommendation	1B3BB5A6-D611-4EDD-9485-D80B38A852FC
584574	HCC	5/19/2022	Jamz22	320	liters	39408	Recommendation	74A96DF4-C80A-4EAA-B3CF-7BD4E8E85328
584574	HCC	5/19/2022	Jamz22	320	liters	39408	Recommendation	9513779A-CF8E-46A5-80BB-A219C04C5841
6594	Bradshaw	5/13/2022	Jamz22	320	liters	17740	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	5/13/2022	Jamz22	320	liters	25408	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	5/13/2022	Jamz22	320	liters	27854	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
6594	Bradshaw	5/13/2022	Bath344	4	liters	222	Recommendation	ABA16DEC-E9A5-4A6F-9809-4221405D3FB6
558899	Bradshaw	5/13/2022	Bath344	4	liters	318	Recommendation	BBBCB2F5-D98A-4610-9240-54780447423F
4857	Bradshaw	5/13/2022	Bath344	4	liters	350	Recommendation	C5E1C569-5193-4C07-9EEE-7E86B920EF97
3699	Bradshaw	5/16/2022	Bath344	2	liters	110	Recommendation	68AAF301-77ED-4688-9A13-106EC575B85E