Forum Discussion
Helper IHow to reference an index from another table in a function
Hey everyone,
Just started using Power BI last week and am running into a roadblock. I have a function, Clean/Format, that needs to be applied to every table in the "Record" column. I want to reference the "Index" value in my function so that when I Invoke a Custom Function it applies to each row respectively rather than only applying to the row with an "Index" value of 1.
Below is the DAX script. Criticisms are appreciated as I am aware that this script is likely inefficient.
Thanks all.
let expand = (tbl) => Table.SplitColumn(Table.TransformColumns(Table.FromList(tbl, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", each Text.Combine(List.ReplaceValue(_,null,"",Replacer.ReplaceValue), "|"), type text}), "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"LocationFacilty","LocationStatus","LocationCity","LocationState","LocationZip","LocationCountry","LocationContactName","LocationContactEmail","LocationContactPhone"}), Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\10examples.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Added Custom" = Table.AddColumn(Sheet1_Sheet, "URL data", each Json.Document( Web.Contents("https://classic.clinicaltrials.gov/api/query/study_fields?expr=AREA[NCTId]" & [Column1] & " &fields=NCTId,BriefTitle,LocationFacility,LocationStatus,LocationCity,LocationState,LocationZip,LocationCountry,LocationContactName,LocationContactEmail,LocationContactPhone&fmt=json"))[StudyFieldsResponse][StudyFields]{0}), #"Expanded URL data" = Table.ExpandRecordColumn(#"Added Custom", "URL data", {"Rank", "NCTId", "BriefTitle", "LocationFacility", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEMail", "LocationContactPhone"}), #"Added Custom1" = Table.AddColumn(#"Expanded URL data", "Custom", each try expand(List.Zip({[LocationFacility],[LocationStatus],[LocationCity],[LocationState],[LocationZip],[LocationCountry],[LocationContactName],[LocationContactEMail],[LocationContactPhone]})) otherwise #table({"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"},{{"", "", "", "", "", "", "", "", ""}})), #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [BriefTitle],each List.First([BriefTitle]),Replacer.ReplaceValue,{"BriefTitle"}), #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Column1", "BriefTitle", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"LocationFacilty", "LocationStatus", "LocationCity", "LocationState", "LocationZip", "LocationCountry", "LocationContactName", "LocationContactEmail", "LocationContactPhone"}) in #"Expanded Custom"This works for the test IDs.
Note 1: because of the empty lists I had to remove nulls with empty strings
Note 2: The second to last ID has no extra data at all, so I had to introduce a dummy stand-in table
19 Replies
- lbendlin
Super User
- This is M script, not DAX
- Row numbers start at 0, not at 1. Change your index definition.
tross4012Thanks for the response.
I changed the index to 0:
The issue is that all tables in the invoked "Clean/Format" column point to "NCT02579226" instead of their row's respective index number.
After clicking on the "Table" record in row 10, it shows the info for row 1 (Index 0).
- lbendlin
please provide sample data that covers your issue. Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- tross4012
Sample Data/Link to the file:
Here is the link to the file, which includes the sample data. The main items of reference here are the "New Source" table and the "Clean/Format" function:
Expected Results:
I click on the "Table" in the "Clean/Format" column for row 1.
This results are correct:
Then I click on the "Table" in the "Clean/Format" column for row 2:
This results are incorrect:
The only way to fix this, is if I manually change the index in my M script from:
Record = #"Added Index"{0}[Record],to:
Record = #"Added Index"{1}[Record],I expect each "Table" in the "Clean/Format" column to display information respective to the value in the NCTId column. See the following example.
I click on the "Table" in the "Clean/Format" column for row 7:
Without MANUALLY changing the M script to:
Record = #"Added Index"{6}[Record],The following table should display:
Please let me know if there is any other information that you need to further understand my question/problem!