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.
Hi,
I have a refrence table in exccel that is created by the user. The table looks like below. I need to generate another table based on this table but with each row repreasenting one peice of data:
A1 A2 A3 From To
MEW 0 20688495 BORID 1 140
20F3301 CHIRT 1 316
20F3301 FOODO 317 450
20F3301 SASY 451 600
MEW 1 20688494 BORID 1 160
20F2124 CHIRT 1 218
20F2124 SASY 219 409
20F2124 FII 410 550
Now, I need to generate the same table but every row from 1 to last digit should be generated, instead of 8 rows I will have 160+550+140+2=852 rows in total. I couldn't think of any automated solution to generate it in PBI,
Please let have your comments and replies to this issue?
Many thanks
What is the logic of 160+550+140+2?
@Ashish_MathurThanks,
I have the ranges as you see in the first post. Now, I need to compare the big table created from these data ranges with another facts table and find the matches and return the values in A3 column. It doesn't sound so right way of doing this job, But I don't know how to use a function like INDEX() MATCH() in Excel or anything which works the same way in Excel?!
Thanks
Hi @Anonymous
I would usually do this sort of thing in Power Query by creating a list column then expanding it.
Here is some sample code which you can tweak to suit your needs. The important steps are highlighted.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCsMgDIZfZeTcQxKj0+O2TuZhCO1gDOn7v8aMK6zUHtTo/5F8WAo87+8TwgCMznsJtpbXPKWxnqRLEJahQCOiMaiPt0eaXitgyPVAzHnMLTzXXexBj/kyf1qmF4c/QmXoLyN7GbdtxMSyk2HyPbBOYgo6D0NPxJQ0Iv0Hq7LLFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A1 = _t, A2 = _t, A3 = _t, From = _t, To = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A1", type text}, {"A2", type text}, {"A3", type text}, {"From", Int64.Type}, {"To", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"A1"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"A1"}), #"Added Custom" = Table.AddColumn(#"Filled Down", "Number", each {[From]..[To]}), #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Number"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range",{{"Number", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"From", "To"}) in #"Removed Columns"
Regards,
Owen
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.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |