Generating a huge table from a compacted table in exccel

[Anonymous]

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

[Ashish_Mathur]

What is the logic of 160+550+140+2?

[Anonymous]

@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

[OwenAuger]

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