Forum Discussion

Emrecan's avatar
Emrecan
Icon for Helper I rankHelper I
4 years ago
Solved

Unifiying ever increasing columm numbers to a single data source

Hello Community I have a situation where each month a new table of data is delivered by email to company.   The table contains columms for that months specific sales info. So each time the report ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this for your Unpivot step

    Table.Unpivot(#"Removed Columns", List.RemoveNulls(List.Transform(Table.ColumnNames(#"Removed Columns"),each try if List.Contains(List.Numbers(1,12),Number.FromText(Text.Split(_,"-"){0})) then _ else null otherwise null)), "Attribute", "Value")

     

Emrecan's avatar
Emrecan
Icon for Helper I rankHelper I
4 years ago

Hello Vijay

Looking at the unpivot code  I was trying to understandt it to use it for other instances.

what is _    in the code here (Text.Split(_,"-"){0})). From what I can gather it means split the current with delimeter "-" and take the first columm?. I couldnt find this kind of usage of text split in documantation. 

 

thanks 

 

 

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

_ means current which you have rightly mentioned. 

Text.Split(_,"-") will split 1-2022 into two parts - 1 and 2022 > {0} will pickup 1st element which is 1 here. In next round, it will be applied on 2-2022 and so on. 

This is little bit advanced but as you move on in your PQ journey, you will understand and implement these.