Grouped Tables excludes some rows/data

[glenreyes]

Hello, Thank you for reviewing my post. I have an issue with data not showing up on my report after I applied the following syntax: {{"ClientName", each Text.Combine([ClientName], ", "), type text}}) in #"Grouped Rows". I Have grouped multiple tables and added a group rows syntax to combine multiple user names and associated them to a single asset. I did this to account for assets that have multiple users assigned to one asset. I suspect that the null values in the clientnames table is causing the issue. I have tried multiple filtering in the UI and still do not get the accurate totals. Any help is appreciated. Thank you.

let

   Source = Sql.Databases("sqlserv1\sqlnch3"),

   WebHelpDesk = Source{[Name="WebHelpDesk"]}[Data],

   dbo_vw_ASSET_INVENTORY_test = WebHelpDesk{[Schema="dbo",Item="vw_ASSET_INVENTORY_test"]}[Data],

   #"Grouped Rows" = Table.Group(dbo_vw_ASSET_INVENTORY_test, {"ASSET_ID", "ASSET_NUMBER", "AUDIT_DATE", "BILLING_RATE_ID", "CLIENT_ID", "CONTRACT_EXPIRATION", "CONTRACT_EXP_ALERTED", "CUSTOM_1", "CUSTOM_2", "CUSTOM_3", "CUSTOM_4", "DELETED", "DEPARTMENT_ID", "DISCOVERY_CONNECTION_ID", "DISCOVERY_KEY", "HAS_CLIENTS", "INITIALIZED_CUSTOM_FIELDS", "INSTALLED_SOFTWARE", "IS_MULTI_INSTALL", "IS_PARENT", "LAST_UPDATED", "LEASE_EXP_ALERTED", "LINKED_PERIPHERALS", "LOCATION_ID", "MAC_ADDRESS", "MODEL_ID", "MULTI_INSTALL_COUNT", "NETWORK_ADDRESS", "NETWORK_NAME", "NOTES", "NOTES_VISIBLE_TO_CLIENTS", "PARENT_ID", "PO_ITEM_ID", "PURCHASE_DATE", "PURCHASE_ORDER_ID", "RESERVABLE", "ROOM_ID", "SERIAL_NUMBER", "SERVICE_CONTRACT", "STATUS_ID", "SUBSCRIBER_ID", "SYNCHRONIZATION_DISABLED", "VERSION", "WARRANTY_EXP_ALERTED", "WARRANTY_TYPE_ID", "ASSET_TYPE", "MODEL_NAME", "MANUFACTURER_ID", "NAME"}, {{"ClientName", each Text.Combine([ClientName], ", "), type text}}) in #"Grouped Rows"

[Anonymous]

@glenreyes,

Please help to share sample data of your original table and post expected result following the guide in the blog below.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

[glenreyes]

Hi and thank you for responding. Below is sample A of the data produced in PBI after grouping was performed. In the sample A there are 8 assets being displayed vs. sample B which has 9 assets which is where I am comparing both my data from. My PBI report is connected to my ticketing system SQL DB. On the Sample A report I already made sure that "Don't Summarize" and "Show Items with no data" is checked to ensure null values are included.

SAMPLE A

SAMPLE B

[Anonymous]

@glenreyes,

I didn't see all the fields you use in Grouped rows step in the above screenshots.

#"Grouped Rows" = Table.Group(dbo_vw_ASSET_INVENTORY_test, {"ASSET_ID", "ASSET_NUMBER", "AUDIT_DATE", "BILLING_RATE_ID", "CLIENT_ID", "CONTRACT_EXPIRATION", "CONTRACT_EXP_ALERTED", "CUSTOM_1", "CUSTOM_2", "CUSTOM_3", "CUSTOM_4", "DELETED", "DEPARTMENT_ID", "DISCOVERY_CONNECTION_ID", "DISCOVERY_KEY", "HAS_CLIENTS", "INITIALIZED_CUSTOM_FIELDS", "INSTALLED_SOFTWARE", "IS_MULTI_INSTALL", "IS_PARENT", "LAST_UPDATED", "LEASE_EXP_ALERTED", "LINKED_PERIPHERALS", "LOCATION_ID", "MAC_ADDRESS", "MODEL_ID", "MULTI_INSTALL_COUNT", "NETWORK_ADDRESS", "NETWORK_NAME", "NOTES", "NOTES_VISIBLE_TO_CLIENTS", "PARENT_ID", "PO_ITEM_ID", "PURCHASE_DATE", "PURCHASE_ORDER_ID", "RESERVABLE", "ROOM_ID", "SERIAL_NUMBER", "SERVICE_CONTRACT", "STATUS_ID", "SUBSCRIBER_ID", "SYNCHRONIZATION_DISABLED", "VERSION", "WARRANTY_EXP_ALERTED", "WARRANTY_TYPE_ID", "ASSET_TYPE", "MODEL_NAME", "MANUFACTURER_ID", "NAME"}, {{"ClientName", each Text.Combine([ClientName], ", "), type text}}) in #"Grouped Rows"

Could you please share sample data of your original table with all fields that can be copied and pasted? I will test it in my Power BI Desktop.


Regards,
Lydia

[glenreyes]

Sorry forgot to add in my post that I am using direct query to my SQL DB to collect the data.