Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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
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
@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
Sorry forgot to add in my post that I am using direct query to my SQL DB to collect the data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.