Home

Power Query in Excel 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-339458%22%20slang%3D%22en-US%22%3EPower%20Query%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339458%22%20slang%3D%22en-US%22%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CPRE%3E%3CSPAN%3EHi%20to%20eveyone%2C%3CBR%20%2F%3EI%20would%20like%20to%20use%20%3A%3CBR%20%2F%3EDim%3C%2FSPAN%3E%20request%24%0Arequest%24%20%3D%20InputBox%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E%22Fully%20pathed%20query%20source...%22%3C%2FSPAN%3E%2C%20%3CSPAN%3E%22Init%20query...%22%3C%2FSPAN%3E%2C%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%0A%26nbsp%3B%0A%20%20ActiveWorkbook.Queries.Add%20Name%3A%3D%3CSPAN%3E%22Banque19%22%3C%2FSPAN%3E%2C%20Formula%3A%3D%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22let%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%20%20%20request%24%20%20%20%2C%20null%2C%20true)%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20Banque19_Sheet%20%3D%20Source%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%5B%3C%2FSPAN%3EItem%3D%22%22Banque19%22%22%2CKind%3D%22%22Sheet%22%22%3CSPAN%20class%3D%22br0%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%5BData%5D%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Type%20modifi%C3%A9%22%3C%2FSPAN%3E%3CSPAN%3E%22%20%3D%20Table.TransformColumnTypes(Banque19_Sheet%2C%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column1%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column2%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column3%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column4%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column5%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%7B%22%3C%2FSPAN%3E%3CSPAN%3E%22C%22%3C%2FSPAN%3E%20%26amp%3B%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22olumn6%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%20type%20number%7D%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column7%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column8%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column9%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column10%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column11%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column12%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column13%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column14%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column15%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column16%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column17%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column18%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column19%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%22%3C%2FSPAN%3E%20%26amp%3B%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column20%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column21%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%7B%22%3C%2FSPAN%3E%3CSPAN%3E%22Column22%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%20type%20number%7D%7D)%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Autres%20colonnes%20supprim%C3%A9es%22%3C%2FSPAN%3E%3CSPAN%3E%22%20%3D%20Table.SelectColumns(%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Type%20modifi%C3%A9%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column1%22%22%2C%20%22%22Column2%22%22%2C%20%22%22Column3%22%22%2C%20%22%22Column4%22%22%2C%20%22%22Column5%22%22%2C%20%22%22Column6%22%22%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E)%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22in%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Autres%20colonnes%20supprim%C3%A9es%22%3C%2FSPAN%3E%3CSPAN%3E%22%22%3CBR%20%2F%3E%3CBR%20%2F%3Eso%20as%20to%20be%20able%20to%20input%20the%20request%2C%20instead%20of%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FPRE%3E%3CPRE%3E%20ActiveWorkbook.Queries.Add%20Name%3A%3D%3CSPAN%3E%22Banque19%22%3C%2FSPAN%3E%2C%20Formula%3A%3D%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22let%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%22%3C%2FSPAN%3E%3CSPAN%3E%22C%3A%5CMyMenu%5CCompta%5CCtaHH2019.xlsm%22%3C%2FSPAN%3E%3CSPAN%3E%22)%2C%20null%2C%20true)%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20Banque19_Sheet%20%3D%20Source%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%5B%3C%2FSPAN%3EItem%3D%22%22Banque19%22%22%2CKind%3D%22%22Sheet%22%22%3CSPAN%20class%3D%22br0%22%3E%5D%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%5BData%5D%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Type%20modifi%C3%A9%22%3C%2FSPAN%3E%3CSPAN%3E%22%20%3D%20Table.TransformColumnTypes(Banque19_Sheet%2C%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column1%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column2%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column3%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column4%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column5%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%7B%22%3C%2FSPAN%3E%3CSPAN%3E%22C%22%3C%2FSPAN%3E%20%26amp%3B%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22olumn6%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%20type%20number%7D%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column7%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column8%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column9%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column10%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column11%22%22%2C%20type%20number%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column12%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column13%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column14%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column15%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column16%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column17%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column18%22%22%2C%20type%20any%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column19%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%22%3C%2FSPAN%3E%20%26amp%3B%20_%0A%20%20%20%20%20%20%3CSPAN%3E%22%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column20%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column21%22%22%2C%20type%20text%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E%2C%20%7B%22%3C%2FSPAN%3E%3CSPAN%3E%22Column22%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%20type%20number%7D%7D)%2C%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Autres%20colonnes%20supprim%C3%A9es%22%3C%2FSPAN%3E%3CSPAN%3E%22%20%3D%20Table.SelectColumns(%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Type%20modifi%C3%A9%22%3C%2FSPAN%3E%3CSPAN%3E%22%2C%3CSPAN%20class%3D%22br0%22%3E%7B%3C%2FSPAN%3E%22%22Column1%22%22%2C%20%22%22Column2%22%22%2C%20%22%22Column3%22%22%2C%20%22%22Column4%22%22%2C%20%22%22Column5%22%22%2C%20%22%22Column6%22%22%3CSPAN%20class%3D%22br0%22%3E%7D%3C%2FSPAN%3E)%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22in%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%26amp%3B%20Chr%3CSPAN%20class%3D%22br0%22%3E(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22br0%22%3E)%3C%2FSPAN%3E%20%26amp%3B%20%3CSPAN%3E%22%20%20%20%20%23%22%3C%2FSPAN%3E%3CSPAN%3E%22Autres%20colonnes%20supprim%C3%A9es%22%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%0A%20%3CBR%20%2F%3Ewhich%20requires%20a%20preset%20%22request%24%22%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20suggestions%2C%3CBR%20%2F%3EHerv%C3%A9%3C%2FPRE%3E%3CPRE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CPRE%3E%26nbsp%3B%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-339458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352188%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352188%22%20slang%3D%22en-US%22%3E%3CP%3EPower%20Query%20cannot%20throw%20up%20a%20dialog%20box%20asking%20for%20input%20like%20VBA%20can%2C%20but%20you%20can%20still%20pass%20variables%20to%20it.%20There%20are%20several%20ways%20to%20do%20it%2C%20but%20this%20is%20the%20easiest%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3COL%3E%3CLI%3ECreate%20a%20table%20that%20is%20one%20record.%20Each%20column%20is%20a%20variable%20you%20want.%20Things%20like%20Start%20Date%2C%20End%20Date%2C%20Customer%20Number%2C%20etc.%20Whatever.%20One%20column%20for%20each%20variable.%3C%2FLI%3E%3CLI%3EEnter%20the%20data%20in%20the%20first%20record%20of%20the%20table.%3C%2FLI%3E%3CLI%3ELoad%20the%20table%20into%20Power%20Query.%20Change%20the%20types%20to%20be%20accurate%2C%20but%20do%20nothing%20else.%3C%2FLI%3E%3CLI%3EFor%20the%20first%20variable%2C%20right-click%20the%20Excel%20table%20query%20in%20step%203%20and%20select%20Reference.%3C%2FLI%3E%3CLI%3ERemove%20all%20columns%20except%20for%20the%20first%20field.%3C%2FLI%3E%3CLI%3ERight-click%20on%20that%20value%2C%20and%20select%20Drill%20Down.%3C%2FLI%3E%3CLI%3EName%20the%20query%20something%20useful%2C%20and%20preferably%20without%20spaces.%20I%20use%20pm%20for%20parameter%2C%20so%20pmStartDate%2C%20pmEndDate%2C%20pmCustomerNumber%2C%20etc.%3C%2FLI%3E%3CLI%3ERepeat%20steps%204-7%20for%20all%20fields%20in%20the%20table.%3C%2FLI%3E%3CLI%3ENow%20you%20can%20reference%20those%20variables%20in%20your%20queries.%20For%20example%2C%20if%20you%20filter%20a%20query%20by%20date%2C%20just%20pick%20a%20bogus%20date.%20it%20will%20show%20something%20like%20%3D%20%23date(2019%2C1%2C1)%20in%20the%20filter.%20Replace%20%22%23date(2019%2C1%2C1)%22%20in%20the%20formula%20bar%20with%20pmStartDate.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20when%20values%20are%20changed%20in%20the%20Excel%20table%2C%20your%20queries%20will%20use%20that%20data%20accordingly.%20You%20can%20use%20these%20in%20filters%2C%20mathematical%20functions%2C%20etc.%20Anywhere%20you%20would%20want%20to%20use%20a%20variable.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Affordsol
Occasional Visitor
Hi to eveyone,
I would like to use :
Dim
request$ request$ = InputBox("Fully pathed query source...", "Init query...", "")   ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents( request$ , null, true)," & Chr(13) & "" & Chr(10) & " Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _ "olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _ ", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""

so as to be able to input the request, instead of
 ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _
      "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\MyMenu\Compta\CtaHH2019.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _
      "olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _
      ", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées"""
 
which requires a preset "request$"

Thanks for your suggestions,
Hervé
 
 
1 Reply

Power Query cannot throw up a dialog box asking for input like VBA can, but you can still pass variables to it. There are several ways to do it, but this is the easiest:

  1. Create a table that is one record. Each column is a variable you want. Things like Start Date, End Date, Customer Number, etc. Whatever. One column for each variable.
  2. Enter the data in the first record of the table.
  3. Load the table into Power Query. Change the types to be accurate, but do nothing else.
  4. For the first variable, right-click the Excel table query in step 3 and select Reference.
  5. Remove all columns except for the first field.
  6. Right-click on that value, and select Drill Down.
  7. Name the query something useful, and preferably without spaces. I use pm for parameter, so pmStartDate, pmEndDate, pmCustomerNumber, etc.
  8. Repeat steps 4-7 for all fields in the table.
  9. Now you can reference those variables in your queries. For example, if you filter a query by date, just pick a bogus date. it will show something like = #date(2019,1,1) in the filter. Replace "#date(2019,1,1)" in the formula bar with pmStartDate.

 

Now when values are changed in the Excel table, your queries will use that data accordingly. You can use these in filters, mathematical functions, etc. Anywhere you would want to use a variable.